package com.dynabook.ems.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dynabook.ems.common.ErrorCode;
import com.dynabook.ems.config.EmsMailProperties;
import com.dynabook.ems.constant.MailTemplate;
import com.dynabook.ems.exception.BusinessException;
import com.dynabook.ems.mapper.EmsCategoryMapper;
import com.dynabook.ems.mapper.EmsMainEngineMapper;
import com.dynabook.ems.mapper.EmsWorkOrderDetailMapper;
import com.dynabook.ems.mapstruct.EmsMapper;
import com.dynabook.ems.model.bo.OldMachineMessageBO;
import com.dynabook.ems.model.bo.TableDataBO;
import com.dynabook.ems.model.dto.*;
import com.dynabook.ems.model.entity.EmsEngineWorkOrder;
import com.dynabook.ems.model.entity.EmsMailQueue;
import com.dynabook.ems.model.entity.EmsMainEngine;
import com.dynabook.ems.model.entity.EmsWorkOrderDetail;
import com.dynabook.ems.model.enums.DetailStatusEnum;
import com.dynabook.ems.model.enums.MailEnum;
import com.dynabook.ems.model.enums.ModelStatusEnum;
import com.dynabook.ems.model.enums.RedisKeyEnum;
import com.dynabook.ems.model.vo.*;
import com.dynabook.ems.model.vo.detailMachine.DetailMachine;
import com.dynabook.ems.service.*;
import com.dynabook.ems.utils.FastExcelUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisCallback;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * EMS主引擎服务实现类
 * @author tester
 * @description 针对表【ems_main_engine】的数据库操作Service实现
 * @createDate 2025-06-05 14:58:22
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class EmsMainEngineServiceImpl extends ServiceImpl<EmsMainEngineMapper, EmsMainEngine>
        implements EmsMainEngineService {
    
    // ==================== 常量定义 ====================
    private static final String MAIL_IMPORT_FROM = "ems_admin@dbh.dynabook.com";
    private static final int LARGE_BATCH_SIZE = 500;
    private static final int MEDIUM_BATCH_SIZE = 200;
    private static final int SMALL_BATCH_SIZE = 100;
    private static final long CACHE_EXPIRE_MINUTES = 10L;
    private static final long SHORT_CACHE_EXPIRE_MINUTES = 2L;
    private static final long SAMPLE_CACHE_EXPIRE_MINUTES = 5L;
    private static final long REDIS_EXPIRE_DAYS = 30L;
    private static final long MAX_WAIT_MINUTES = 10L;
    
    // 允许的排序字段
    private static final List<String> VALID_SORT_FIELDS = Arrays.asList(
            "oa_request_id", "type", "SERIAL_NO", "total_num",
            "unitprice", "model_status", "instore_date"
    );
    
    // ==================== 配置属性 ====================
    @Value("${spring.ems_url}")
    private String emsUrl;
    
    private String mainBody; // 邮件主体内容
    
    // ==================== 依赖注入 ====================
    private final EmsMailQueueService emsMailQueueService;
    private final EmsMailProperties emsMailProperties;
    private final EmsCategoryMapper emsCategoryMapper;
    private final EmsEbsDataService emsEbsDataService;
    private final EmsWorkOrderDetailMapper emsWorkOrderDetailMapper;
    private final EmsEngineWorkOrderService emsEngineWorkOrderService;
    private final MailTemplate mailTemplate;
    private final RedisTemplate<String, Object> redisTemplate;
    private final WorkOrderStatusSyncService workOrderStatusSyncService;
    
    @Resource(name = "DataGeneratorThreadPool")
    private ThreadPoolExecutor dataGeneratorThreadPool;
    
    @Resource(name = "InventoryExportThreadPool")
    private ThreadPoolExecutor inventoryExportThreadPool;

    // ==================== 初始化方法 ====================
    @PostConstruct
    private void init() {
        mainBody = buildEmailMainBody();
    }
    
    private String buildEmailMainBody() {
        return "<p>Dear All</p>" +
               "<p>大家好, 以下样品已入库, 如果有需要可通过申请再来领取</p>" +
               "<p>点击<a style=\"font-size:20px;\" href=\"" + emsUrl + "\">此链接</a>确认详情</p>";
    }

    // ==================== 核心业务方法 ====================
    @Transactional(rollbackFor = Exception.class)
    @Override
    public Boolean updateEmsMainEngine(List<EmsDataDTO> list) {
        if (CollUtil.isEmpty(list)) {
            log.warn("批量更新EmsMainEngine失败：输入列表为空");
            return false;
        }
        
        log.info("开始批量更新EmsMainEngine，数据条数: {}", list.size());
        
        try {
            List<EmsMainEngine> engineList = convertAndSetFixedNo(list);
            boolean result = saveBatchOptimized(engineList);
            
            log.info("批量更新EmsMainEngine完成，结果: {}, 处理数量: {}", result, engineList.size());
            return result;
        } catch (Exception e) {
            log.error("批量更新EmsMainEngine异常，数据条数: {}", list.size(), e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "批量更新失败: " + e.getMessage());
        }
    }
    
    private List<EmsMainEngine> convertAndSetFixedNo(List<EmsDataDTO> list) {
        return list.stream()
                .map(item -> {
                    EmsMainEngine engine = EmsMapper.INSTANCE.EmsDataDTOToEmsMainEngine(item);
                    if (engine.getFixedNo() == null) {
                        engine.setFixedNo(this.baseMapper.getFixedNo());
                        log.debug("为记录生成新的fixedNo: {}", engine.getFixedNo());
                    }
                    return engine;
                })
                .collect(Collectors.toList());
    }
    
    private boolean saveBatchOptimized(List<EmsMainEngine> engineList) {
        if (engineList.size() > LARGE_BATCH_SIZE) {
            return this.saveOrUpdateBatch(engineList, LARGE_BATCH_SIZE);
        } else {
            return this.saveOrUpdateBatch(engineList);
        }
    }

    @Override
    public String getFixNo() {
        log.debug("获取新的fixedNo");
        String fixedNo = this.baseMapper.getFixedNo();
        log.debug("获取到的fixedNo: {}", fixedNo);
        return fixedNo;
    }

    @Override
    public Page<ReceiptListVO> getReceiptList(ReceiptPageDTO receiptPageDTO) {
        validateReceiptPageDTO(receiptPageDTO);
        
        log.info("获取收据列表，参数: {}", receiptPageDTO);
        
        // 参数预处理和校验
        String searchText = receiptPageDTO.getSearchText();
        int current = receiptPageDTO.getCurrent();
        int pageSize = receiptPageDTO.getPageSize();
        String sortField = validateAndGetSortField(receiptPageDTO.getSortField());
        String sortOrder = validateAndGetSortOrder(receiptPageDTO.getSortOrder());
        List<String> isRepeats = receiptPageDTO.getIsRepeats();

        // 状态列表
        List<Integer> statusList = Arrays.asList(
                ModelStatusEnum.BEFORE_EDIT.getValue(),
                ModelStatusEnum.BEFORE_STORE.getValue()
        );
        
        Page<ReceiptListVO> page = new Page<>(current, pageSize);
        
        log.debug("执行查询，searchText: {}, statusList: {}, sortField: {}, sortOrder: {}",
                searchText, statusList, sortField, sortOrder);
        
        Page<ReceiptListVO> result = this.baseMapper.getReceiptListWithUser(
                page, searchText, statusList, sortField, sortOrder,isRepeats
        );
        
        log.info("获取收据列表完成，总记录数: {}, 总页数: {}", result.getTotal(), result.getPages());
        return result;
    }
    
    private void validateReceiptPageDTO(ReceiptPageDTO receiptPageDTO) {
        if (receiptPageDTO == null) {
            log.error("receiptPageDTO为空");
            throw new IllegalArgumentException("receiptPageDTO不能为空");
        }
    }
    
    private String validateAndGetSortField(String sortField) {
        if (sortField != null && !isValidSortField(sortField)) {
            log.warn("无效的排序字段: {}, 已重置为null", sortField);
            return null;
        }
        return sortField;
    }
    
    private String validateAndGetSortOrder(String sortOrder) {
        if (sortOrder != null && !"ASC".equalsIgnoreCase(sortOrder) && !"DESC".equalsIgnoreCase(sortOrder)) {
            log.warn("无效的排序顺序: {}, 已重置为null", sortOrder);
            return null;
        }
        return sortOrder;
    }

    @Override
    public ReceiptUpdateDTO getSingleMachine(String fixNO) {
        if (StrUtil.isBlank(fixNO)) {
            log.warn("getSingleMachine方法：fixNO为空");
            return null;
        }
        
        log.info("获取单个机器信息，fixNO: {}", fixNO);
        
        // 尝试从缓存获取
        ReceiptUpdateDTO cachedResult = getFromCache("machine_info:" + fixNO, ReceiptUpdateDTO.class);
        if (cachedResult != null) {
            log.debug("从缓存获取机器信息: {}", fixNO);
            return cachedResult;
        }
        
        // 缓存未命中，查询数据库
        ReceiptUpdateDTO receiptUpdateDTO = this.baseMapper.getSingleMachine(fixNO);
        if (receiptUpdateDTO == null) {
            log.warn("未找到fixNO: {}的机器信息", fixNO);
            return null;
        }
        
        // 设置状态文本
        String modelStatus = ModelStatusEnum.getTextByValue(Integer.valueOf(receiptUpdateDTO.getModelStatus()));
        receiptUpdateDTO.setModelStatus(modelStatus);
        
        // 存入缓存
        saveToCache("machine_info:" + fixNO, receiptUpdateDTO, CACHE_EXPIRE_MINUTES);
        
        log.debug("成功获取fixNO: {}的机器信息", fixNO);
        return receiptUpdateDTO;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateReceiptData(ReceiptUpdateDTO receiptUpdateDTO) {
        String fixedNo = receiptUpdateDTO.getFixedNo();
        log.info("更新单个机器信息，fixedNo: {}", fixedNo);

        if (StrUtil.isBlank(fixedNo)) {
            log.error("fixedNo为空，无法更新");
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "fixedNo不能为空");
        }
        
        try {
            // 清除相关缓存
            clearMachineInfoCache(fixedNo);

            // 构建更新实体
            EmsMainEngine engine = buildUpdateEngine(receiptUpdateDTO);
            
            // 执行更新操作
            LambdaUpdateWrapper<EmsMainEngine> updateWrapper = new LambdaUpdateWrapper<>();
            updateWrapper.eq(EmsMainEngine::getFixedNo, fixedNo);
            
            boolean result = this.update(engine, updateWrapper);
            log.info("更新机器信息完成，fixedNo: {}, 结果: {}", fixedNo, result);
            return result;
        } catch (Exception e) {
            log.error("更新机器信息异常，fixedNo: {}", fixedNo, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "更新机器信息失败: " + e.getMessage());
        }
    }
    
    /**
     * 构建更新实体对象
     */
    private EmsMainEngine buildUpdateEngine(ReceiptUpdateDTO dto) {
        EmsMainEngine engine = new EmsMainEngine();
        
        // 使用工具方法设置非空字段
        setStringField(engine::setModelName, dto.getModelName());
        setStringField(engine::setCategory, dto.getCategory());
        setStringField(engine::setSerialNo, dto.getSerialNo());
        setStringField(engine::setCpu, dto.getCpu());
        setStringField(engine::setHdd, dto.getHdd());
        setStringField(engine::setMemory, dto.getMemory());
        setStringField(engine::setType, dto.getType());
        setStringField(engine::setScreenSize, dto.getScreenSize());
        setStringField(engine::setCdRom, dto.getCdRom());
        setStringField(engine::setInvoiceNo, dto.getInvoiceNo());
        setStringField(engine::setLocation, dto.getLocation());
        setStringField(engine::setRemark, dto.getRemark());
        setStringField(engine::setSerialNumber, dto.getSerialNumber());
        setStringField(engine::setSupplier, dto.getSupplier());
        setStringField(engine::setBigCategory, dto.getBigCategory());
        setStringField(engine::setWorkerOrder, dto.getWorkerOrder());
        setStringField(engine::setReturnOrder, dto.getReturnOrder());
        setStringField(engine::setMotherboard, dto.getMotherboard());
        setStringField(engine::setUnitPrice, dto.getUnitPrice());
        setStringField(engine::setOaRequestId, dto.getOaRequestId());
        
        // 设置数值字段
        setPositiveIntegerField(engine::setTotalNum, dto.getTotalNum());
        setPositiveIntegerField(engine::setDepartment, dto.getDepartment());
        setPositiveIntegerField(engine::setSectionManager, dto.getSectionManager());
        setPositiveIntegerField(engine::setBroken, dto.getBroken());
        setPositiveIntegerField(engine::setCanBorrow, dto.getCanBorrow());
        setPositiveIntegerField(engine::setInventoryNum, dto.getInventoryNum());
        setPositiveIntegerField(engine::setUsedNum, dto.getUsedNum());
        setPositiveIntegerField(engine::setScrapNum, dto.getScrapNum());
        
        // 设置状态
        if (dto.getUpdateType() == 1) {
            engine.setModelStatus(ModelStatusEnum.BEFORE_STORE.getValue());
        } else {
            engine.setModelStatus(ModelStatusEnum.MODIFICATION_BEFORE_STORE.getValue());
        }
        
        return engine;
    }
    
    /**
     * 设置字符串字段的工具方法
     */
    private void setStringField(java.util.function.Consumer<String> setter, String value) {
        if (StrUtil.isNotBlank(value)) {
            setter.accept(value);
        }
    }
    
    /**
     * 设置正整数字段的工具方法
     */
    private void setPositiveIntegerField(java.util.function.Consumer<Integer> setter, Integer value) {
        if (value != null && value > 0) {
            setter.accept(value);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateMachineList(UpdateMaicheListDTO updateMaicheListDTO) {
        List<String> fixedNoList = updateMaicheListDTO.getFixedNoList();
        log.info("批量更新机器列表，fixedNo数量: {}", fixedNoList != null ? fixedNoList.size() : 0);

        if (CollUtil.isEmpty(fixedNoList)) {
            log.error("fixedNoList为空，无法更新");
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "fixedNoList不能为空");
        }

        try {
            // 批量清除缓存
            fixedNoList.forEach(this::clearMachineInfoCache);

            // 构建批量更新实体列表
            List<EmsMainEngine> updateList = buildBatchUpdateEngines(updateMaicheListDTO, fixedNoList);

            // 执行批量更新
            boolean success = updateBatchByIdOptimized(updateList);
            
            log.info("批量更新机器列表完成，结果: {}, 更新数量: {}", success, updateList.size());
            return success;
        } catch (Exception e) {
            log.error("批量更新机器列表异常", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "批量更新失败: " + e.getMessage());
        }
    }
    
    /**
     * 构建批量更新实体列表
     */
    private List<EmsMainEngine> buildBatchUpdateEngines(UpdateMaicheListDTO dto, List<String> fixedNoList) {
        return fixedNoList.stream().map(fixedNo -> {
            EmsMainEngine engine = new EmsMainEngine();
            engine.setFixedNo(fixedNo);

            // 设置字段值
            setStringField(engine::setModelName, dto.getModelName());
            setStringField(engine::setCategory, dto.getCategory());
            setStringField(engine::setBigCategory, dto.getBigCategory());
            setStringField(engine::setLocation, dto.getLocation());
            setStringField(engine::setSupplier, dto.getSupplier());
            setStringField(engine::setCpu, dto.getCpu());
            setStringField(engine::setMemory, dto.getMemory());
            setStringField(engine::setMotherboard, dto.getMotherboard());
            setStringField(engine::setHdd, dto.getHdd());
            setStringField(engine::setCdRom, dto.getCdRom());
            setStringField(engine::setScreenSize, dto.getScreenSize());

            // 设置数值字段
            if (dto.getCanBorrow() != null) {
                engine.setCanBorrow(dto.getCanBorrow());
            }
            if (dto.getDepartment() != null) {
                engine.setDepartment(dto.getDepartment());
            }
            if (dto.getSectionManager() != null) {
                engine.setSectionManager(dto.getSectionManager());
            }

            // 设置状态
            if (dto.getUpdateType() == 1) {
                engine.setModelStatus(ModelStatusEnum.BEFORE_STORE.getValue());
            } else {
                engine.setModelStatus(ModelStatusEnum.MODIFICATION_BEFORE_STORE.getValue());
            }
            
            return engine;
        }).collect(Collectors.toList());
    }
    
    /**
     * 优化的批量更新方法
     */
    private boolean updateBatchByIdOptimized(List<EmsMainEngine> updateList) {
        if (updateList.size() > SMALL_BATCH_SIZE) {
            return this.updateBatchById(updateList, SMALL_BATCH_SIZE);
        } else {
            return this.updateBatchById(updateList);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean Receipt(FixNoDTO fixNoDTO) {
        validateReceiptRequest(fixNoDTO);
        
        try {
            List<String> fixedNoList = fixNoDTO.getFixedNo();
            log.info("开始处理入库操作，固定资产编号数量: {}", fixedNoList.size());
            
            // 获取入库数据
            List<TableDataBO> tableDataBOList = this.baseMapper.getTableData(fixedNoList);
            log.info("入库数据: {}", tableDataBOList);
            
            if (CollUtil.isEmpty(tableDataBOList)) {
                log.warn("未找到对应的TableData数据，固定资产编号: {}", fixedNoList);
                throw new BusinessException(ErrorCode.NOT_FOUND_ERROR, "未找到对应的入库数据");
            }

            // 创建并保存邮件队列
            boolean saveMailResult = createAndSaveMailQueue(fixNoDTO, tableDataBOList);
            
            // 更新主引擎状态
            boolean updateStatusResult = updateMainEngineStatus(fixedNoList);
            
            logReceiptResult(fixNoDTO, saveMailResult, updateStatusResult, fixedNoList);
            
            return saveMailResult && updateStatusResult;
        } catch (BusinessException e) {
            throw e;
        } catch (Exception e) {
            log.error("处理Receipt时发生错误: {}", e.getMessage(), e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "入库操作失败: " + e.getMessage());
        }
    }
    
    private void validateReceiptRequest(FixNoDTO fixNoDTO) {
        if (fixNoDTO == null || CollUtil.isEmpty(fixNoDTO.getFixedNo())) {
            log.warn("Receipt方法接收到空的fixNoDTO或fixedNo列表");
            throw new BusinessException(ErrorCode.PARAMS_ERROR, "入库请求参数不能为空");
        }
    }
    
    private boolean createAndSaveMailQueue(FixNoDTO fixNoDTO, List<TableDataBO> tableDataBOList) {
        String jsonResult = convertTableDataToJson(tableDataBOList);
        log.info("转换后的JSON数据: {}", jsonResult);

        EmsMailQueue emsMailQueue = new EmsMailQueue();
        emsMailQueue.setType(MailEnum.IMPORT.getValue());
        emsMailQueue.setMainBody(mainBody);
        emsMailQueue.setFrom(MAIL_IMPORT_FROM);
        emsMailQueue.setSubject("[样品入库通知]" + fixNoDTO.getSubject());
        emsMailQueue.setTo(JSONUtil.toJsonStr(emsMailProperties.getImportTo()));
        emsMailQueue.setTableData(jsonResult);
        
        boolean result = emsMailQueueService.save(emsMailQueue);
        log.info("邮件队列保存结果: {}", result);
        return result;
    }
    
    private boolean updateMainEngineStatus(List<String> fixedNoList) {
        UpdateWrapper<EmsMainEngine> updateWrapper = new UpdateWrapper<>();
        updateWrapper.in("fixed_no", fixedNoList);
        updateWrapper.set("model_status", ModelStatusEnum.IN_STORE.getValue());
        return this.update(updateWrapper);
    }
    
    private void logReceiptResult(FixNoDTO fixNoDTO, boolean saveMailResult, boolean updateStatusResult, List<String> fixedNoList) {
        if (fixNoDTO.getUpdateType() == 1) {
            log.info("更新主引擎状态结果: {}, 影响记录数: {}, EBS数据入库", updateStatusResult, fixedNoList.size());
        } else {
            log.info("更新主引擎状态结果: {}, 影响记录数: {}, 改造品数据入库", updateStatusResult, fixedNoList.size());
        }
    }

    @Override
    public DetailMachine getDetailMachine(String fixedNo) {
        if (StrUtil.isBlank(fixedNo)) {
            log.warn("getDetailMachine方法：fixedNo为空");
            return null;
        }
        
        try {
            DetailMachine detailMachine = this.baseMapper.getDetailMachine(fixedNo);
            if (detailMachine != null && detailMachine.getStatus() != null) {
                String statusText = ModelStatusEnum.getTextByValue(Integer.valueOf(detailMachine.getStatus()));
                detailMachine.setStatus(statusText);
            }
            return detailMachine;
        } catch (Exception e) {
            log.error("获取机器详情失败，fixedNo: {}", fixedNo, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "获取机器详情失败");
        }
    }

    @Override
    public CategoryMachine getCategoryByItemCode(String itemCode) {
        if (StrUtil.isBlank(itemCode)) {
            log.warn("getCategoryByItemCode方法：itemCode为空");
            return null;
        }
        
        try {
            CategoryMachine result = emsCategoryMapper.getCategoryByItemCode(itemCode);
            if (result == null) {
                log.warn("未找到对应的CategoryMachine数据，itemCode: {}", itemCode);
            } else {
                log.info("找到对应的CategoryMachine数据，itemCode: {}, result: {}", itemCode, result);
            }
            return result;
        } catch (Exception e) {
            log.error("根据itemCode获取分类失败，itemCode: {}", itemCode, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "获取分类信息失败");
        }
    }

    @Override
    public Boolean updateUserName() {
        int pageSize = 1000; // 每页数据量
        int batchSize = 200; // 每批保存的数据量

        // 先查询总数，避免无限分页
        QueryWrapper<EmsMainEngine> countWrapper = new QueryWrapper<>();
        countWrapper.eq("model_status", 3);
        long totalCount = this.baseMapper.selectCount(countWrapper);

        if (totalCount == 0) {
            log.info("没有需要更新的数据");
            return false;
        }

        // 计算需要处理的页数
        long totalPages = (totalCount + pageSize - 1) / pageSize;
        log.info("开始更新用户名数据，总数据量：{}，分为{}页处理", totalCount, totalPages);

        // 使用原子计数器记录成功数量
        AtomicInteger successCounter = new AtomicInteger(0);

        // 创建CountDownLatch等待所有任务完成
        CountDownLatch latch = new CountDownLatch((int) totalPages);

        // 按页提交任务到线程池
        for (int i = 1; i <= totalPages; i++) {
            final int currentPage = i;
            dataGeneratorThreadPool.execute(() -> {
                try {
                    log.debug("处理第{}页数据", currentPage);

                    // 查询当前页数据
                    LambdaQueryWrapper<EmsMainEngine> queryWrapper = new LambdaQueryWrapper<>();
                    queryWrapper.eq(EmsMainEngine::getModelStatus, 3);
                    queryWrapper.select(EmsMainEngine::getUserName, EmsMainEngine::getFixedNo, EmsMainEngine::getStartDate);
                    Page<EmsMainEngine> page = new Page<>(currentPage, pageSize);
                    Page<EmsMainEngine> pageResult = this.baseMapper.selectPage(page, queryWrapper);
                    log.info("pageResult: {}", pageResult);
                    // 获取当前页的记录列表
                    List<EmsMainEngine> emsMainEngineList = pageResult.getRecords();

                    // 创建当前批次的结果列表
//                    List<EmsCurrentUse> emsCurrentUseList = new ArrayList<>();
//                    log.info("emsMainEngineList: {}", emsMainEngineList);
//                    // 使用Stream API处理数据
//                    emsMainEngineList.stream()
//                        .filter(Objects::nonNull)
//                        .map(engine -> {
//                            EmsCurrentUse emsCurrentUse = new EmsCurrentUse();
//                            emsCurrentUse.setUserName(Optional.ofNullable(engine.getUserName()).orElse(""));
//                            emsCurrentUse.setFixedNo(Optional.ofNullable(engine.getFixedNo()).orElse(""));
//                            emsCurrentUse.setBorrowTime(engine.getStartDate());
//                            // 设置借用数量默认值为1
//                            emsCurrentUse.setBorrowNum(1);
//                            return emsCurrentUse;
//                        })
//                        .filter(use -> !use.getFixedNo().isEmpty())
//                        .forEach(emsCurrentUseList::add);
//                    log.info("emsCurrentUseList: {}", emsCurrentUseList);
//                    // 分批保存数据
//                    for (int j = 0; j < emsCurrentUseList.size(); j += batchSize) {
//                        int end = Math.min(j + batchSize, emsCurrentUseList.size());
//                        List<EmsCurrentUse> subList = emsCurrentUseList.subList(j, end);
//                        if (emsCurrentUseService.saveBatch(subList)) {
//                            successCounter.addAndGet(subList.size());
//                        } else {
//                            log.error("保存第{}页第{}批数据失败", currentPage, j / batchSize + 1);
//                        }
//                    }
                } catch (Exception e) {
                    log.error("处理第{}页数据时发生错误", currentPage, e);
                } finally {
                    latch.countDown();
                }
            });
        }

        try {
            // 等待所有任务完成，最多等待10分钟
            boolean completed = latch.await(MAX_WAIT_MINUTES, TimeUnit.MINUTES);
            if (!completed) {
                log.warn("部分数据处理超时未完成");
            }
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            log.error("等待数据处理完成时被中断", e);
        }

        int finalSuccessCount = successCounter.get();
        log.info("用户名数据更新完成，总数据量：{}，成功处理：{}", totalCount, finalSuccessCount);
        return finalSuccessCount > 0;
    }

    @Override
    public Page<MachineListVO> getMachineList(MachineListDTO machineListDTO) {
        String sortField = Optional.ofNullable(machineListDTO.getSortField())
                .filter(field -> !field.trim().isEmpty())
                .orElse("fixed_no");
        String sortOrder = Optional.ofNullable(machineListDTO.getSortOrder())
                .filter(order -> !order.trim().isEmpty())
                .orElse("desc");
        machineListDTO.setSortField(sortField);
        machineListDTO.setSortOrder(sortOrder);
        String user_name = machineListDTO.getUser_name();
        String history_user = machineListDTO.getHistory_user();
        Page<MachineListVO> page = new Page<>(machineListDTO.getCurrent(), machineListDTO.getPageSize());
        String searchType = machineListDTO.getSearch_type();
        if (StrUtil.isBlank(searchType)) {
            searchType = "1";
        }
        if (searchType.equals("1")) {
            Page<MachineListVO> machineListVOPage = this.baseMapper.pageMachineListWithSearch(page, machineListDTO);
            return machineListVOPage;
        } else {
            if (StrUtil.isNotBlank(history_user) && StrUtil.isNotBlank(user_name)) {
                log.error("history_user和user_name不能同时搜索：{}", history_user, user_name);
                return null;
            } else if (StrUtil.isNotBlank(history_user) && StrUtil.isBlank(user_name)) {
                // 创建Page对象
                Page<MachineListVO> machineListVOPage = this.baseMapper.pageMachineListWithHistoryUser(page, machineListDTO);
                // 传入Page对象作为第一个参数
                return machineListVOPage;
            } else if (StrUtil.isNotBlank(user_name) && StrUtil.isBlank(history_user)) {
                Page<MachineListVO> machineListVOPage = this.baseMapper.pageMachineListWithUserName(page, machineListDTO);
                return machineListVOPage;
            } else {
                Page<MachineListVO> machineListVOPage = this.baseMapper.pageMachineList(page, machineListDTO);
                return machineListVOPage;
            }
        }

    }

    @Override
    public boolean handleMissingItems(List<ItemCodeVO> itemCodeList) {
        if (CollectionUtil.isEmpty(itemCodeList)) {
            return false;
        } else {
            // 使用Stream将itemCodeList转换为List<Map<String, String>>
            List<Map<String, String>> list = itemCodeList.stream()
                    .map(itemCode -> {
                        Map<String, String> map = new HashMap<>();
                        map.put("name", itemCode.getItemCode());
                        map.put("oa_request_id",itemCode.getOaRequestId());
                        return map;
                    })
                    .collect(Collectors.toList());
            EmsMailQueue emsMailQueue = new EmsMailQueue();
            emsMailQueue.setSubject(mailTemplate.getReceiptSubject());
            emsMailQueue.setMainBody(mailTemplate.getReceiptApply());
            emsMailQueue.setFrom(MAIL_IMPORT_FROM);
            emsMailQueue.setTo(JSONUtil.toJsonStr(emsMailProperties.getReceiptTo()));
            emsMailQueue.setType(MailEnum.RECEIPT.getValue());
            String tableData = JSONUtil.toJsonStr(list);
            emsMailQueue.setTableData(tableData);
            return emsMailQueueService.save(emsMailQueue);
        }
    }

    /**
     * 将TableDataBO列表转换为指定格式的JSON
     *
     * @param tableDataList TableDataBO列表
     * @return JSON字符串
     */
    private String convertTableDataToJson(List<TableDataBO> tableDataList) {
        if (tableDataList == null || tableDataList.isEmpty()) {
            return "[]";
        }
        JSONArray jsonArray = new JSONArray();
        for (TableDataBO data : tableDataList) {
            JSONObject jsonObject = JSONUtil.createObj()
                    .put("id", data.getFixed_no())
                    .put("name", data.getMODEL_NAME())
                    .put("sn", data.getSERIAL_NO())
                    .put("pn", data.getType())
                    .put("section", data.getSectionName())
                    .put("remark", Optional.ofNullable(data.getRemark()).orElse(""))
                    .put("category", data.getCategory())
                    .put("bigCategory", data.getBigCategory())
                    .put("totalNum", data.getTotalNum())
                    .put("charge", data.getCharge());
            jsonArray.add(jsonObject);
        }
        return jsonArray.toString();
    }

    // 添加排序字段校验方法
    private boolean isValidSortField(String sortField) {
        return VALID_SORT_FIELDS.stream()
                .anyMatch(field -> field.equalsIgnoreCase(sortField)); // 修改：使用大小写不敏感比较
    }

    @Override
    public Page<SampleListVO> getSampleListByType(SampleListRequest request) {
        log.info("根据样品型号查询样品列表，type: {}, workOrderNumber: {}, searchText: {}, current: {}, pageSize: {}", 
                request.getType(), request.getWorkOrderNumber(), request.getSearchText(), request.getCurrent(), request.getPageSize());
        
        // 使用缓存获取已选择的样品编号
        final Set<String> finalSelectedFixedNos = getSelectedFixedNosFromCache(request.getWorkOrderNumber());
        
        int currentPage = request.getCurrent();
        int pageSize = request.getPageSize();
        
        // 构建最终结果
        Page<SampleListVO> finalResult = new Page<>(currentPage, pageSize);
        List<SampleListVO> finalRecords = new ArrayList<>();
        
        if (currentPage == 1 && !finalSelectedFixedNos.isEmpty()) {
            // 第一页：优先显示已选择的样品
            List<SampleListVO> selectedSamples = getSelectedSamplesOptimized(request, finalSelectedFixedNos);
            finalRecords.addAll(selectedSamples);
            
            // 计算第一页还需要多少未选择的样品
            int remainingSlots = pageSize - selectedSamples.size();
            
            if (remainingSlots > 0) {
                // 查询未选择的样品来填充剩余位置
                Page<SampleListVO> unselectedPage = new Page<>(1, remainingSlots);
                Page<SampleListVO> unselectedResult = this.baseMapper.pageSampleListByType(unselectedPage, request);
                
                // 过滤出未选择的样品并设置状态
                List<SampleListVO> unselectedSamples = unselectedResult.getRecords().stream()
                        .filter(sample -> !finalSelectedFixedNos.contains(sample.getFixedNo()))
                        .limit(remainingSlots)
                        .peek(sample -> sample.setIs_select(0))
                        .collect(Collectors.toList());
                
                finalRecords.addAll(unselectedSamples);
                
                // 计算总记录数：已选择的 + 未选择的总数（使用缓存的count查询）
                long unselectedTotal = getUnselectedSampleCount(request, finalSelectedFixedNos);
                finalResult.setTotal(selectedSamples.size() + unselectedTotal);
            } else {
                // 已选择的样品就足够填满第一页
                finalResult.setTotal(selectedSamples.size());
                if (selectedSamples.size() > pageSize) {
                    finalRecords = finalRecords.subList(0, pageSize);
                }
            }
        } else {
            // 非第一页或没有已选择样品：直接查询并设置状态
            Page<SampleListVO> page = new Page<>(currentPage, pageSize);
            Page<SampleListVO> result = this.baseMapper.pageSampleListByType(page, request);
            
            // 设置选择状态
            result.getRecords().forEach(sample -> {
                sample.setIs_select(finalSelectedFixedNos.contains(sample.getFixedNo()) ? 1 : 0);
            });
            
            finalRecords = result.getRecords();
            finalResult.setTotal(result.getTotal());
        }
        
        finalResult.setRecords(finalRecords);
        finalResult.setCurrent(currentPage);
        finalResult.setSize(pageSize);
        
        log.info("查询完成，当前页: {}, 总记录数: {}, 总页数: {}, 当前页记录数: {}", 
                currentPage, finalResult.getTotal(), finalResult.getPages(), finalRecords.size());
        
        return finalResult;
    }
    
    /**
     * 获取工单已选择的样品编号集合（带缓存优化）
     */
    private Set<String> getSelectedFixedNosFromCache(String workOrderNumber) {
        if (StrUtil.isBlank(workOrderNumber)) {
            return new HashSet<>();
        }
        
        try {
            // 先从Redis缓存获取
            String cacheKey = "selected_samples:" + workOrderNumber;
            Set<String> cachedResult = (Set<String>) redisTemplate.opsForValue().get(cacheKey);
            
            if (cachedResult != null) {
                log.debug("从缓存获取工单 {} 已绑定样品数量: {}", workOrderNumber, cachedResult.size());
                return cachedResult;
            }
            
            // 缓存未命中，查询数据库
            Set<String> selectedFixedNos = getSelectedFixedNos(workOrderNumber);
            
            // 存入缓存，过期时间5分钟
            redisTemplate.opsForValue().set(cacheKey, selectedFixedNos, SAMPLE_CACHE_EXPIRE_MINUTES, TimeUnit.MINUTES);
            
            return selectedFixedNos;
        } catch (Exception e) {
            log.warn("缓存操作失败，直接查询数据库: {}", e.getMessage());
            return getSelectedFixedNos(workOrderNumber);
        }
    }
    
    /**
     * 获取工单已选择的样品编号集合（原始方法）
     */
    private Set<String> getSelectedFixedNos(String workOrderNumber) {
        try {
            LambdaQueryWrapper<EmsEngineWorkOrder> workOrderWrapper = new LambdaQueryWrapper<>();
            workOrderWrapper.eq(EmsEngineWorkOrder::getWorkOrderNumber, workOrderNumber)
                           .eq(EmsEngineWorkOrder::getIsDelete, 0)
                           .select(EmsEngineWorkOrder::getFixedNo); // 只查询需要的字段
            List<EmsEngineWorkOrder> workOrderList = emsEngineWorkOrderService.list(workOrderWrapper);
            
            Set<String> selectedFixedNos = workOrderList.stream()
                    .map(EmsEngineWorkOrder::getFixedNo)
                    .collect(Collectors.toSet());
            
            log.debug("工单 {} 已绑定样品数量: {}", workOrderNumber, selectedFixedNos.size());
            return selectedFixedNos;
        } catch (Exception e) {
            log.warn("查询工单绑定样品失败: {}", e.getMessage());
            return new HashSet<>();
        }
    }
    
    /**
     * 获取已选择样品的详细信息（优化版本）
     */
    private List<SampleListVO> getSelectedSamplesOptimized(SampleListRequest request, Set<String> selectedFixedNos) {
        if (selectedFixedNos.isEmpty()) {
            return new ArrayList<>();
        }
        
        try {
            // 使用新的Mapper方法来查询已选择样品的详细信息，包括课室名称
            List<String> fixedNoList = new ArrayList<>(selectedFixedNos);
            List<SampleListVO> selectedSamples = this.baseMapper.getSelectedSamplesWithSectionManager(fixedNoList, request.getType());
            
            log.debug("查询到已选择样品详细信息数量: {}", selectedSamples.size());
            return selectedSamples;
        } catch (Exception e) {
            log.warn("查询已选择样品详细信息失败: {}", e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 获取未选择样品的总数量（带缓存）
     */
    private long getUnselectedSampleCount(SampleListRequest request, Set<String> selectedFixedNos) {
        try {
            // 使用缓存key
            String cacheKey = String.format("unselected_count:%s:%s:%s", 
                    request.getType(), 
                    request.getSearchText() != null ? request.getSearchText() : "", 
                    selectedFixedNos.size());
            
            Long cachedCount = (Long) redisTemplate.opsForValue().get(cacheKey);
            if (cachedCount != null) {
                log.debug("从缓存获取未选择样品总数: {}", cachedCount);
                return cachedCount;
            }
            
            // 缓存未命中，查询数据库
            LambdaQueryWrapper<EmsMainEngine> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(EmsMainEngine::getType, request.getType())
                   .in(EmsMainEngine::getModelStatus, Arrays.asList(0, 9, 14))
                   .gt(EmsMainEngine::getInventoryNum, 0)
                   .notIn(!selectedFixedNos.isEmpty(), EmsMainEngine::getFixedNo, selectedFixedNos);
            
            if (StrUtil.isNotBlank(request.getSearchText())) {
                wrapper.and(w -> w.like(EmsMainEngine::getFixedNo, request.getSearchText())
                                .or()
                                .like(EmsMainEngine::getModelName, request.getSearchText()));
            }
            
            long count = this.count(wrapper);
            
            // 存入缓存，过期时间2分钟
            redisTemplate.opsForValue().set(cacheKey, count, SHORT_CACHE_EXPIRE_MINUTES, TimeUnit.MINUTES);
            
            return count;
        } catch (Exception e) {
            log.warn("查询未选择样品总数失败: {}", e.getMessage());
            return 0;
        }
    }
    
    /**
     * 获取已选择样品的详细信息（已优化，但保留以兼容旧代码）
     * @deprecated 此方法已被优化，建议使用getSelectedSamplesOptimized方法
     */
    @Deprecated
    private List<SampleListVO> getSelectedSamplesDetails(SampleListRequest request, Set<String> selectedFixedNos) {
        try {
            if (selectedFixedNos.isEmpty()) {
                return new ArrayList<>();
            }
            
            // 使用新的Mapper方法来查询已选择样品的详细信息，包括课室名称
            List<String> fixedNoList = new ArrayList<>(selectedFixedNos);
            List<SampleListVO> selectedSamples = this.baseMapper.getSelectedSamplesWithSectionManager(fixedNoList, request.getType());
            
            log.debug("查询到已选择样品详细信息数量: {}", selectedSamples.size());
            return selectedSamples;
        } catch (Exception e) {
            log.warn("查询已选择样品详细信息失败: {}", e.getMessage());
            return new ArrayList<>();
        }
    }

    @Override
    public Page<SampleListVO> getSampleListByWorkOrder(WorkOrderSampleListRequest request) {
        log.info("根据工单号查询绑定样品列表，workOrderNumber: {}, searchText: {}", request.getWorkOrderNumber(), request.getSearchText());
        
        Page<SampleListVO> page = new Page<>(request.getCurrent(), request.getPageSize());
        Page<SampleListVO> result = this.baseMapper.pageSampleListByWorkOrder(page, request);
        
        log.info("查询完成，总记录数: {}, 总页数: {}", result.getTotal(), result.getPages());
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean selectSamplesForWorkOrder(WorkOrderSelectSampleRequest request) {
        log.info("工单选择样品，workOrderNumber: {}, totalNum: {}, sampleCount: {}", 
                request.getWorkOrderNumber(), request.getTotalNum(), request.getSampleList().size());
        
        // 1. 验证样品数量总和是否等于需求总数
        int actualTotalNum = request.getSampleList().stream()
                .mapToInt(WorkOrderSelectSampleRequest.SampleSelectItem::getBorrowNum)
                .sum();
        
        if (actualTotalNum != request.getTotalNum()) {
            log.error("样品数量验证失败，需求总数: {}, 实际选择总数: {}", request.getTotalNum(), actualTotalNum);
            return false;
        }
        
        try {
            // 2. 查询当前工单下已存在的样品关联记录
            LambdaQueryWrapper<EmsEngineWorkOrder> existingWrapper = new LambdaQueryWrapper<>();
            existingWrapper.eq(EmsEngineWorkOrder::getWorkOrderNumber, request.getWorkOrderNumber())
                          .eq(EmsEngineWorkOrder::getDetailId, request.getDetailId())
                          .eq(EmsEngineWorkOrder::getIsDelete, 0);
            List<EmsEngineWorkOrder> existingWorkOrders = emsEngineWorkOrderService.list(existingWrapper);
            
            // 获取现有的样品编号集合
            Set<String> existingFixedNos = existingWorkOrders.stream()
                    .map(EmsEngineWorkOrder::getFixedNo)
                    .collect(Collectors.toSet());
            
            // 获取新选择的样品编号集合
            Set<String> newFixedNos = request.getSampleList().stream()
                    .map(WorkOrderSelectSampleRequest.SampleSelectItem::getFixedNo)
                    .collect(Collectors.toSet());
            
            log.info("现有样品数量: {}, 新选择样品数量: {}", existingFixedNos.size(), newFixedNos.size());
            
            // 3. 找出需要删除的样品（存在于现有记录中，但不在新选择中）
            Set<String> toDeleteFixedNos = new HashSet<>(existingFixedNos);
            toDeleteFixedNos.removeAll(newFixedNos);
            
            // 4. 找出需要添加的样品（存在于新选择中，但不在现有记录中）
            Set<String> toAddFixedNos = new HashSet<>(newFixedNos);
            toAddFixedNos.removeAll(existingFixedNos);
            
            log.info("需要删除的样品数量: {}, 需要添加的样品数量: {}", toDeleteFixedNos.size(), toAddFixedNos.size());
            
            // 5. 删除不再选择的样品关联记录
            if (!toDeleteFixedNos.isEmpty()) {
                LambdaQueryWrapper<EmsEngineWorkOrder> deleteWrapper = new LambdaQueryWrapper<>();
                deleteWrapper.eq(EmsEngineWorkOrder::getWorkOrderNumber, request.getWorkOrderNumber())
                            .eq(EmsEngineWorkOrder::getDetailId, request.getDetailId())
                            .in(EmsEngineWorkOrder::getFixedNo, toDeleteFixedNos);
                boolean deleteResult = emsEngineWorkOrderService.remove(deleteWrapper);
                if (!deleteResult) {
                    throw new BusinessException(ErrorCode.SYSTEM_ERROR, "删除工单样品关联关系失败");
                }
                log.info("成功删除 {} 条工单样品关联记录", toDeleteFixedNos.size());
            }
            
            // 6. 添加新选择的样品关联记录
            List<EmsEngineWorkOrder> workOrderList = new ArrayList<>();
            String categoryName = "";
            
            // 处理新增的样品
            for (WorkOrderSelectSampleRequest.SampleSelectItem item : request.getSampleList()) {
                // 只处理需要新增的样品
                if (!toAddFixedNos.contains(item.getFixedNo())) {
                    continue;
                }
                
                // 验证样机编号是否存在
                EmsMainEngine engine = this.getOne(
                        new LambdaQueryWrapper<EmsMainEngine>()
                                .eq(EmsMainEngine::getFixedNo, item.getFixedNo())
                );
                
                if (engine == null) {
                    throw new BusinessException(ErrorCode.NOT_FOUND_ERROR, 
                            "样机编号不存在: " + item.getFixedNo());
                }
                EmsEngineWorkOrder workOrder = new EmsEngineWorkOrder();
                // 如果是在库样品，检查库存是否足够
                if (item.getType() == 0) {
                    if (engine.getInventoryNum() == null || engine.getInventoryNum() < item.getBorrowNum()) {
                        throw new BusinessException(ErrorCode.PARAMS_ERROR, 
                                "样机 " + item.getFixedNo() + " 库存不足，当前库存: " + 
                                (engine.getInventoryNum() != null ? engine.getInventoryNum() : 0) + 
                                "，需求数量: " + item.getBorrowNum());
                    }
                }else{
                    workOrder.setOldEmsRequestId(item.getEmsRequestId());
                }
                // 创建工单样品关联记录
                workOrder.setWorkOrderNumber(request.getWorkOrderNumber());
                workOrder.setFixedNo(item.getFixedNo());
                workOrder.setDetailId(request.getDetailId());
                workOrder.setBorrowNum(item.getBorrowNum());
                workOrder.setIsMy(item.getType());
                workOrder.setInsertTime(LocalDateTime.now());
                workOrder.setUpdateTime(LocalDateTime.now());
                workOrder.setIsDelete(0);
                workOrderList.add(workOrder);
                
                // 更新bom detail的信息
                categoryName = engine.getCategory();
            }
            
            // 7. 更新BOM详情
            EmsWorkOrderDetail emsWorkOrderDetail=new EmsWorkOrderDetail();
            emsWorkOrderDetail.setId(request.getDetailId());
            if (StrUtil.isNotBlank(categoryName)) {
                emsWorkOrderDetail.setCategory(categoryName);
            }
            emsWorkOrderDetail.setDetailStatus(DetailStatusEnum.SELECTED.getValue());
            emsWorkOrderDetail.setTotalNum(request.getTotalNum());
            emsWorkOrderDetail.setWorkOrderNumber(request.getWorkOrderNumber());
            emsWorkOrderDetail.setUpdateTime(LocalDateTime.now());
            int updateResult = emsWorkOrderDetailMapper.updateById(emsWorkOrderDetail);
            if (updateResult != 1) {
                throw new BusinessException(ErrorCode.SYSTEM_ERROR, "更新BOM详情失败");
            }
            
            // 8. 批量保存新的关联关系
            if (!workOrderList.isEmpty()) {
                boolean saveResult = emsEngineWorkOrderService.saveBatch(workOrderList);
                if (!saveResult) {
                    throw new BusinessException(ErrorCode.SYSTEM_ERROR, "保存工单样品关联关系失败");
                }
            }
            
            // 9. 所有数据库操作成功后，再操作Redis
            try {
                // 先删除该detailId的所有数据，再重新添加
                removeDetailFromRedis(request.getWorkOrderNumber(), request.getDetailId());
                
                // 如果有新的样品选择，则添加到Redis
                if (!newFixedNos.isEmpty()) {
                    saveDetailToRedis(request.getWorkOrderNumber(), request.getDetailId(), new ArrayList<>(newFixedNos));
                }
                
                // 新增：处理工单审批Hash存储
                updateWorkOrderApprovalHash(request.getWorkOrderNumber(), newFixedNos);
                
                // 清除样品选择缓存
                clearSelectedSamplesCache(request.getWorkOrderNumber());
                
                // 重新初始化工单状态同步缓存（特别是在重新选择样品后）
                if (!toAddFixedNos.isEmpty() || !toDeleteFixedNos.isEmpty()) {
                    log.info("检测到样品变更，重新初始化工单状态缓存: workOrderNumber={}", request.getWorkOrderNumber());
                    workOrderStatusSyncService.initializeWorkOrderCache(request.getWorkOrderNumber());
                }
                
            } catch (Exception redisException) {
                // Redis操作失败，记录日志但不影响主流程
                log.error("Redis操作失败，但数据库操作已成功，工单号: {}, 错误: {}", 
                        request.getWorkOrderNumber(), redisException.getMessage(), redisException);
            }
            
            log.info("工单选择样品完成，workOrderNumber: {}, 删除记录数: {}, 新增记录数: {}, 更新类别: {}", 
                    request.getWorkOrderNumber(), toDeleteFixedNos.size(), workOrderList.size(), categoryName);
            return true;
            
        } catch (BusinessException e) {
            log.error("工单选择样品业务异常: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("工单选择样品系统异常", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "工单选择样品失败: " + e.getMessage());
        }
    }
    
    /**
     * 从Redis中删除指定detailId的数据
     * 
     * @param workOrderNumber 工单号
     * @param detailId 详情ID
     */
    private void removeDetailFromRedis(String workOrderNumber, Long detailId) {
        try {
            String redisKey = RedisKeyEnum.WORKER_ORDER_DEATIL_PREFIX.getValue()+workOrderNumber;
            String detailIdStr = String.valueOf(detailId);
            
            // 获取现有的Redis数据
            Map<String, List<String>> workOrderMap = (Map<String, List<String>>) redisTemplate.opsForValue().get(redisKey);
            if (workOrderMap == null || workOrderMap.isEmpty()) {
                log.info("Redis中没有找到工单数据，无需删除，工单号: {}", workOrderNumber);
                return;
            }
            
            // 删除指定detailId的数据
            List<String> removedFixedNos = workOrderMap.remove(detailIdStr);
            
            // 更新Redis
            if (workOrderMap.isEmpty()) {
                // 如果删除后没有数据了，直接删除这个key
                redisTemplate.delete(redisKey);
                log.info("Redis删除key成功，工单号: {}", workOrderNumber);
            } else {
                // 否则更新数据
                redisTemplate.opsForValue().set(redisKey, workOrderMap);
                redisTemplate.expire(redisKey, REDIS_EXPIRE_DAYS, TimeUnit.DAYS);
            }
            
            log.info("Redis删除detailId成功，工单号: {}, detailId: {}, 删除的fixedNo数量: {}", 
                    workOrderNumber, detailId, removedFixedNos != null ? removedFixedNos.size() : 0);
            
        } catch (Exception e) {
            log.error("Redis删除detailId失败，工单号: {}, detailId: {}, 错误: {}", workOrderNumber, detailId, e.getMessage(), e);
        }
    }
    
    /**
     * 将指定detailId和fixedNo列表存储到Redis
     * 
     * @param workOrderNumber 工单号
     * @param detailId 详情ID
     * @param fixedNos fixedNo列表
     */
    private void saveDetailToRedis(String workOrderNumber, Long detailId, List<String> fixedNos) {
        try {
            String redisKey = RedisKeyEnum.WORKER_ORDER_DEATIL_PREFIX.getValue()+workOrderNumber;
            String detailIdStr = String.valueOf(detailId);
            
            // 获取现有的Redis数据
            Map<String, List<String>> workOrderMap = (Map<String, List<String>>) redisTemplate.opsForValue().get(redisKey);
            if (workOrderMap == null) {
                workOrderMap = new HashMap<>();
            }
            
            // 添加或更新detailId对应的fixedNo列表
            workOrderMap.put(detailIdStr, new ArrayList<>(fixedNos));
            
            // 存储到Redis
            redisTemplate.opsForValue().set(redisKey, workOrderMap);
            redisTemplate.expire(redisKey, REDIS_EXPIRE_DAYS, TimeUnit.DAYS);
            
            log.info("Redis存储detailId成功，工单号: {}, detailId: {}, fixedNo数量: {}", 
                    workOrderNumber, detailId, fixedNos.size());
            
        } catch (Exception e) {
            log.error("Redis存储detailId失败，工单号: {}, detailId: {}, 错误: {}", workOrderNumber, detailId, e.getMessage(), e);
        }
    }
    
    /**
     * 从Redis获取指定detailId的fixedNo列表
     * 
     * @param workOrderNumber 工单号
     * @param detailId 详情ID
     * @return fixedNo列表
     */
    public List<String> getDetailFromRedis(String workOrderNumber, Long detailId) {
        try {
            String redisKey = RedisKeyEnum.WORKER_ORDER_DEATIL_PREFIX.getValue()+workOrderNumber;
            String detailIdStr = String.valueOf(detailId);
            
            Map<String, List<String>> workOrderMap = (Map<String, List<String>>) redisTemplate.opsForValue().get(redisKey);
            if (workOrderMap != null && workOrderMap.containsKey(detailIdStr)) {
                return workOrderMap.get(detailIdStr);
            }
            return new ArrayList<>();
        } catch (Exception e) {
            log.error("Redis获取detailId失败，工单号: {}, detailId: {}, 错误: {}", workOrderNumber, detailId, e.getMessage(), e);
            return new ArrayList<>();
        }
    }
    
    /**
     * 从Redis获取整个工单的数据
     * 
     * @param workOrderNumber 工单号
     * @return 工单数据Map
     */
    public Map<String, List<String>> getWorkOrderFromRedis(String workOrderNumber) {
        try {
            String redisKey = RedisKeyEnum.WORKER_ORDER_DEATIL_PREFIX.getValue()+workOrderNumber;
            Map<String, List<String>> workOrderMap = (Map<String, List<String>>) redisTemplate.opsForValue().get(redisKey);
            return workOrderMap != null ? workOrderMap : new HashMap<>();
        } catch (Exception e) {
            log.error("Redis获取工单失败，工单号: {}, 错误: {}", workOrderNumber, e.getMessage(), e);
            return new HashMap<>();
        }
    }
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean cancelWorkOrder(String workOrderNumber) {
        log.info("开始取消工单，工单号: {}", workOrderNumber);
        
        try {
            // 1. 检查Redis中是否存在该工单号的键
            String redisKey = RedisKeyEnum.WORKER_ORDER_DEATIL_PREFIX.getValue()+workOrderNumber;
            Map<String, List<String>> workOrderMap = (Map<String, List<String>>) redisTemplate.opsForValue().get(redisKey);
            
            // 2. 如果Redis中存在数据，则需要删除ems_engine_work_order关系表中的记录
            if (workOrderMap != null && !workOrderMap.isEmpty()) {
                log.info("Redis中找到工单数据，开始删除关系表记录，工单号: {}", workOrderNumber);
                
                // 直接根据工单号删除所有相关的ems_engine_work_order记录
                LambdaQueryWrapper<EmsEngineWorkOrder> deleteWrapper = new LambdaQueryWrapper<>();
                deleteWrapper.eq(EmsEngineWorkOrder::getWorkOrderNumber, workOrderNumber);
                
                boolean deleteResult = emsEngineWorkOrderService.remove(deleteWrapper);
                if (!deleteResult) {
                    log.error("删除工单样品关联关系失败，工单号: {}", workOrderNumber);
                    throw new BusinessException(ErrorCode.SYSTEM_ERROR, "删除工单样品关联关系失败");
                }
                
                log.info("成功删除工单样品关联记录，工单号: {}", workOrderNumber);
            } else {
                log.info("Redis中未找到工单数据，无需删除关系表记录，工单号: {}", workOrderNumber);
            }
            
            // 3. 更新ems_bom_detail表中对应工单号的记录
            LambdaUpdateWrapper<EmsWorkOrderDetail> updateWrapper = new LambdaUpdateWrapper<>();
            updateWrapper.eq(EmsWorkOrderDetail::getWorkOrderNumber, workOrderNumber)
                        .eq(EmsWorkOrderDetail::getIsDelete, 0)
                        .set(EmsWorkOrderDetail::getDetailStatus, DetailStatusEnum.BEFORE_SELECTED.getValue())
                        .set(EmsWorkOrderDetail::getCategory, null)
                        .set(EmsWorkOrderDetail::getTotalNum, null)
                        .set(EmsWorkOrderDetail::getWorkOrderNumber, null)
                        .set(EmsWorkOrderDetail::getUpdateTime, LocalDateTime.now());
            
            int updateResult = emsWorkOrderDetailMapper.update(null, updateWrapper);
            if (updateResult <= 0){
                log.error("更新BOM详情失败，工单号: {}", workOrderNumber);
                throw new BusinessException(ErrorCode.SYSTEM_ERROR, "更新BOM详情失败");
            }
            
            // 4. 删除Redis中的整个工单key
            if (workOrderMap != null && !workOrderMap.isEmpty()) {
                redisTemplate.delete(redisKey);
                log.info("成功删除Redis中的工单数据，工单号: {}", workOrderNumber);
            }
            
            log.info("成功取消工单，工单号: {}", workOrderNumber);
            return true;
            
        } catch (BusinessException e) {
            log.error("取消工单业务异常，工单号: {}, 错误: {}", workOrderNumber, e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("取消工单系统异常，工单号: {}", workOrderNumber, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "取消工单失败: " + e.getMessage());
        }
    }
    
    /**
     * 清除样品选择缓存
     * 
     * @param workOrderNumber 工单号
     */
    private void clearSelectedSamplesCache(String workOrderNumber) {
        if (StrUtil.isBlank(workOrderNumber)) {
            return;
        }
        try {
            String cacheKey = "selected_samples:" + workOrderNumber;
            redisTemplate.delete(cacheKey);
            log.debug("清除工单样品选择缓存: {}", workOrderNumber);
        } catch (Exception e) {
            log.warn("清除样品选择缓存失败，workOrderNumber: {}, 错误: {}", workOrderNumber, e.getMessage());
        }
    }
    
    /**
     * 清除机器信息缓存
     * 
     * @param fixedNo 固定资产编号
     */
    private void clearMachineInfoCache(String fixedNo) {
        if (StrUtil.isBlank(fixedNo)) {
            return;
        }
        try {
            String cacheKey = "machine_info:" + fixedNo;
            redisTemplate.delete(cacheKey);
            log.debug("清除机器信息缓存: {}", fixedNo);
        } catch (Exception e) {
            log.warn("清除机器信息缓存失败，fixedNo: {}, 错误: {}", fixedNo, e.getMessage());
        }
    }
    
    /**
     * 更新工单审批Hash存储
     * 
     * @param workOrderNumber 工单号
     * @param newFixedNos 新选择的样品编号集合
     */
    private void updateWorkOrderApprovalHash(String workOrderNumber, Set<String> newFixedNos) {
        try {
            String redisKey = RedisKeyEnum.WORKER_ORDER_APPROVAL_PREFIX.getValue() + workOrderNumber;
            
            // 检查key是否存在
            Boolean keyExists = redisTemplate.hasKey(redisKey);
            
            if (!keyExists) {
                // key不存在，直接插入所有新样品编号
                for (String fixedNo : newFixedNos) {
                    redisTemplate.opsForHash().put(redisKey, fixedNo, 0);
                }
                redisTemplate.expire(redisKey, REDIS_EXPIRE_DAYS, TimeUnit.DAYS);
                log.info("Redis Hash新建成功，工单号: {}, 插入样品数量: {}", workOrderNumber, newFixedNos.size());
            } else {
                // key存在，需要比较现有的和新提交的样品编号
                Set<Object> existingFixedNos = redisTemplate.opsForHash().keys(redisKey);
                Set<String> existingFixedNoStrings = existingFixedNos.stream()
                        .map(Object::toString)
                        .collect(Collectors.toSet());
                
                // 找出需要新增的样品编号（在新提交中但不在Redis中）
                Set<String> toAddFixedNos = new HashSet<>(newFixedNos);
                toAddFixedNos.removeAll(existingFixedNoStrings);
                
                // 找出需要删除的样品编号（在Redis中但不在新提交中）
                Set<String> toDeleteFixedNos = new HashSet<>(existingFixedNoStrings);
                toDeleteFixedNos.removeAll(newFixedNos);
                
                // 插入新的样品编号
                for (String fixedNo : toAddFixedNos) {
                    redisTemplate.opsForHash().put(redisKey, fixedNo, 0);
                }
                
                // 删除不再需要的样品编号
                if (!toDeleteFixedNos.isEmpty()) {
                    redisTemplate.opsForHash().delete(redisKey, toDeleteFixedNos.toArray());
                }
                
                log.info("Redis Hash更新成功，工单号: {}, 新增样品数量: {}, 删除样品数量: {}", 
                        workOrderNumber, toAddFixedNos.size(), toDeleteFixedNos.size());
            }
            
        } catch (Exception e) {
            log.error("更新工单审批Hash失败，工单号: {}, 错误: {}", workOrderNumber, e.getMessage(), e);
        }
    }

    @Override
    public Page<ProductListVO> getProductList(ProductListRequest request) {
        log.info("查询成品列表，请求参数: {}", request);
        
        // 创建分页对象
        Page<ProductListVO> page = new Page<>(request.getCurrent(), request.getPageSize());
        
        // 调用Mapper进行分页查询
        Page<ProductListVO> result = this.baseMapper.pageProductList(page, request);
        
        // 处理状态文本转换
        if (result != null && result.getRecords() != null) {
            result.getRecords().forEach(item -> {
                if (item.getModelStatus() != null) {
                    item.setModelStatusText(ModelStatusEnum.getTextByValue(item.getModelStatus()));
                }
            });
        }
        
        log.info("查询成品列表完成，总数: {}", result.getTotal());
        return result;
    }

    @Override
    public Page<ProductListVO> getOldProductList(ProductListRequest request) {
        log.info("查询旧品入库列表，请求参数: {}", request);
        
        // 创建分页对象
        Page<ProductListVO> page = new Page<>(request.getCurrent(), request.getPageSize());
        
        // 调用Mapper进行分页查询
        Page<ProductListVO> result = this.baseMapper.pageOldProductList(page, request);
        
        // 处理状态文本转换
        if (result != null && result.getRecords() != null) {
            result.getRecords().forEach(item -> {
                if (item.getModelStatus() != null) {
                    item.setModelStatusText(ModelStatusEnum.getTextByValue(item.getModelStatus()));
                }
            });
        }
        
        log.info("查询旧品入库列表完成，总数: {}", result.getTotal());
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean deleteReceiptData(DeleteReceiptRequest deleteReceiptRequest) {
        log.info("删除资产数据，请求参数: {}", deleteReceiptRequest);
        
        try {
            List<String> fixedNos = deleteReceiptRequest.getFixedNos();
            if (CollUtil.isEmpty(fixedNos)) {
                log.warn("删除请求的样品编号列表为空");
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "删除请求的样品编号列表不能为空");
            }
            
            // 检查样品编号的is_repeat字段
            log.info("开始检查样品编号的is_repeat字段，样品数量: {}", fixedNos.size());
            LambdaQueryWrapper<EmsMainEngine> checkWrapper = new LambdaQueryWrapper<>();
            checkWrapper.in(EmsMainEngine::getFixedNo, fixedNos)
                       .select(EmsMainEngine::getFixedNo, EmsMainEngine::getIsRepeat);
            
            List<EmsMainEngine> checkResults = this.list(checkWrapper);
            
            // 检查是否存在is_repeat为0的记录
            List<String> nonRepeatableFixedNos = checkResults.stream()
                    .filter(engine -> engine.getIsRepeat() != null && engine.getIsRepeat() == 0)
                    .map(EmsMainEngine::getFixedNo)
                    .collect(Collectors.toList());
            
            if (!nonRepeatableFixedNos.isEmpty()) {
                log.error("存在不可删除的样品编号(is_repeat=0): {}", nonRepeatableFixedNos);
                throw new BusinessException(ErrorCode.OPERATION_ERROR, 
                        "以下样品编号不属于重复数据，不允许删除: " + String.join(", ", nonRepeatableFixedNos));
            }
            
            log.info("is_repeat字段检查通过，所有样品均可删除");
            
            // 执行删除操作
            int deletedFromMain = this.baseMapper.deleteReceiptDataFromMainEngine(fixedNos);
            int deletedFromEbs = this.baseMapper.deleteReceiptDataFromEbsData(fixedNos);
            
            log.info("删除资产数据完成，main引擎删除数量: {}, EBS数据删除数量: {}", deletedFromMain, deletedFromEbs);
            
            // 删除操作成功完成，即使没有找到要删除的记录也是正常的
            return true;
        } catch (BusinessException e) {
            // 业务异常直接抛出，触发事务回滚
            throw e;
        } catch (Exception e) {
            log.error("删除资产数据失败，错误: {}", e.getMessage(), e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "删除资产数据失败: " + e.getMessage());
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean deleteReceiptDataBatch(DeleteReceiptRequest deleteReceiptRequest) {
        log.info("批量删除资产数据，请求参数: {}", deleteReceiptRequest);
        try {
            int i = this.baseMapper.deleteBatchIds(deleteReceiptRequest.getFixedNos());
            boolean remove = emsEbsDataService.removeByIds(deleteReceiptRequest.getFixedNos());
            if( !(i > 0 && remove)){
                log.error("批量删除资产数据失败"+deleteReceiptRequest.getFixedNos());
              throw  new  BusinessException(ErrorCode.SYSTEM_ERROR, "批量删除资产数据失败: ");
            }
            return true;
        } catch (Exception e) {
            log.error("批量删除资产数据失败"+deleteReceiptRequest.getFixedNos());
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "批量删除资产数据失败: " + e.getMessage());
        }
    }

    @Override
    public Boolean isExistSn(String sn) {
        if (StrUtil.isBlank(sn)) {
            log.warn("isExistSn方法：sn为空");
            return true; // 空值认为存在，不允许使用
        }
        
        try {
            // 使用 count 方法统计数量，避免多条记录时抛出异常
            long count = this.count(
                new LambdaQueryWrapper<EmsMainEngine>().eq(EmsMainEngine::getSerialNo, sn)
            );
            // 返回 true 表示不存在（count == 0），false 表示已存在（count > 0）
            return count == 0;
        } catch (Exception e) {
            log.error("检查序列号是否存在失败，sn: {}", sn, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "检查序列号是否存在失败: " + e.getMessage());
        }
    }

    @Override
    public Boolean isExistTypeAndSerialNumber(String type, String oaRequestId) {
        try {
            // 使用 count 方法统计数量，避免多条记录时抛出异常
            long count = this.count(
                    new LambdaQueryWrapper<EmsMainEngine>()
                            .eq(EmsMainEngine::getType, type)
                            .eq(EmsMainEngine::getSerialNumber, oaRequestId)
            );
            // 返回 true 表示不存在（count == 0），false 表示已存在（count > 0）
            return count == 0;
        } catch (Exception e) {
            log.error("检查型号和样本号是否存在失败，type: {}, oaRequestId: {}", type, oaRequestId, e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "检查型号和样本号是否存在失败: " + e.getMessage());
        }
    }

    @Override
    public OldMachineMessageBO getSameMessage(String modelType) {
        return this.baseMapper.getSameMessage(modelType);
    }

    @Override
    public void exportInventoryData(String startDateStr, String endDateStr, HttpServletResponse response) {
        log.info("开始导出盘点数据，开始日期: {}, 结束日期: {}", startDateStr, endDateStr);
        long startTime = System.currentTimeMillis();
        
        try {
            // 参数校验
            if (StrUtil.isBlank(startDateStr) || StrUtil.isBlank(endDateStr)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "开始日期和结束日期不能为空");
            }
            
            // 解析日期字符串，捕获格式异常
            LocalDate startDate;
            LocalDate endDate;
            try {
                startDate = LocalDate.parse(startDateStr);
                endDate = LocalDate.parse(endDateStr);
            } catch (Exception e) {
                log.error("日期格式解析失败: startDate={}, endDate={}", startDateStr, endDateStr, e);
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "日期格式错误，请使用yyyy-MM-dd格式");
            }
            
            // 校验开始日期不能晚于结束日期
            if (startDate.isAfter(endDate)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "开始日期不能晚于结束日期");
            }

            // 将LocalDate转换为LocalDateTime（一天的开始和结束）
            LocalDateTime startDateTime = startDate.atStartOfDay();
            LocalDateTime endDateTime = endDate.atTime(23, 59, 59);
            
            // ========== 第一个Sheet: 料件基本资料 ==========
            // 需要过滤的状态值列表
            List<Integer> excludeStatusList = Arrays.asList(
                ModelStatusEnum.BEFORE_EDIT.getValue(),          // 7
                ModelStatusEnum.BEFORE_STORE.getValue(),          // 8
                ModelStatusEnum.CHANGE_BEFORE_EDIT.getValue(),    // 10
                ModelStatusEnum.CHANGE_BEFORE_STORE.getValue(),   // 11
                ModelStatusEnum.MODIFICATION_BEFORE_EDIT.getValue(), // 15
                ModelStatusEnum.MODIFICATION_BEFORE_STORE.getValue()  // 16
            );
            List<String> ExcludeDatas=emsWorkOrderDetailMapper.getWorkOrderDData();
            // ========== 性能优化：在SQL层面进行数据过滤 ==========
            long queryStartTime = System.currentTimeMillis();
            
            LocalDateTime filterDate = LocalDateTime.of(2025, 10, 30, 23, 59, 59);
            Integer scrapedStatus = ModelStatusEnum.SCRAPED.getValue();
            
            // 查询Sheet1数据：排除（入库日期<=2025-10-20 且 状态为SCRAPED）的数据
            LambdaQueryWrapper<EmsMainEngine> queryWrapper1 = new LambdaQueryWrapper<>();
            queryWrapper1.select(
                    EmsMainEngine::getType,
                    EmsMainEngine::getBigCategory,
                    EmsMainEngine::getCategory,
                    EmsMainEngine::getWorkOrderNumber,
                            EmsMainEngine::getCanWorkOrder,
                    EmsMainEngine::getUnitPrice,
                    EmsMainEngine::getInstoreDate,      // 必须查询
                    EmsMainEngine::getModelStatus       // 必须查询
                )
                .le(EmsMainEngine::getInstoreDate, endDateTime)
//                    .ne(EmsMainEngine::getType,"-")
                .notIn(EmsMainEngine::getModelStatus, excludeStatusList)
                    .ne(EmsMainEngine::getCanWorkOrder,2)
                // SQL层面排除：入库日期<=2025-10-20 且 状态为SCRAPED（使用NOT更直观）
                .not(wrapper -> wrapper
                    .le(EmsMainEngine::getInstoreDate, filterDate)      // 入库日期 <= 2025-10-20
                    .eq(EmsMainEngine::getModelStatus, scrapedStatus)   // 且 状态 = SCRAPED
                );
            
            // 只有当排除数据列表不为空时才添加 notIn 条件（避免生成 "NOT IN ()" 的SQL语法错误）
            if (ExcludeDatas != null && !ExcludeDatas.isEmpty()) {
                queryWrapper1.notIn(EmsMainEngine::getFixedNo, ExcludeDatas);
            }
            
            // 查询Sheet3数据：排除状态为SCRAPED的数据
            LambdaQueryWrapper<EmsMainEngine> queryWrapper3 = new LambdaQueryWrapper<>();
            queryWrapper3.select(
                    EmsMainEngine::getType,
                    EmsMainEngine::getLocation,
                    EmsMainEngine::getTotalNum,
                    EmsMainEngine::getScrapNum,
                    EmsMainEngine::getCanWorkOrder,
                    EmsMainEngine::getUnitPrice,
                    EmsMainEngine::getInstoreDate,      // 用于查询条件
                    EmsMainEngine::getModelStatus       // 用于查询条件
                )
                .le(EmsMainEngine::getInstoreDate, endDateTime)
                    .ne(EmsMainEngine::getCanWorkOrder,2)
//                    .ne(EmsMainEngine::getType,"-")
                .notIn(EmsMainEngine::getModelStatus, excludeStatusList)
                .ne(EmsMainEngine::getModelStatus, scrapedStatus);  // SQL层面排除SCRAPED状态
            
            // 只有当排除数据列表不为空时才添加 notIn 条件（避免生成 "NOT IN ()" 的SQL语法错误）
            if (ExcludeDatas != null && !ExcludeDatas.isEmpty()) {
                queryWrapper3.notIn(EmsMainEngine::getFixedNo, ExcludeDatas);
            }
            
            // 并行查询两个数据集
            CompletableFuture<List<EmsMainEngine>> future1 = CompletableFuture.supplyAsync(
                () -> this.list(queryWrapper1), 
                inventoryExportThreadPool
            );
            
            CompletableFuture<List<EmsMainEngine>> future3 = CompletableFuture.supplyAsync(
                () -> this.list(queryWrapper3), 
                inventoryExportThreadPool
            );
            
            // 等待查询完成
            List<EmsMainEngine> rawSheet1Data = future1.join();
            List<EmsMainEngine> filteredList2 = future3.join();
            
            // 应用层按 type 去重（Sheet1）- 保留第一条
            Map<String, EmsMainEngine> sheet1UniqueMap = new java.util.concurrent.ConcurrentHashMap<>();
            List<EmsMainEngine> filteredList1 = rawSheet1Data.stream()
                .filter(item -> {
                    String type = item.getType();
                    if (type == null || type.isEmpty()) {
                        return false;
                    }
                    // putIfAbsent 返回 null 表示是第一次put，返回非null表示已存在
                    return sheet1UniqueMap.putIfAbsent(type, item) == null;
                })
                .collect(Collectors.toList());
            
            long queryEndTime = System.currentTimeMillis();
            
            if (filteredList1.isEmpty() && filteredList2.isEmpty()) {
                throw new BusinessException(ErrorCode.NOT_FOUND_ERROR, "没有找到符合条件的数据");
            }
            
            log.info("SQL层面过滤完成，耗时: {}ms - Sheet1数据: {} 条, Sheet3数据: {} 条", 
                    (queryEndTime - queryStartTime), filteredList1.size(), filteredList2.size());
            
            // ========== 并行处理Sheet1和Sheet3的数据转换 ==========
            long convertStartTime = System.currentTimeMillis();
            int cpuCores = Runtime.getRuntime().availableProcessors();
            
            // 优化批次大小：动态调整批次大小以平衡并行度和开销
            // 数据量大时使用较大批次减少线程切换开销，数据量小时使用较小批次提高并行度
            int batchSize1 = calculateOptimalBatchSize(filteredList1.size(), cpuCores);
            int batchSize3 = calculateOptimalBatchSize(filteredList2.size(), cpuCores);
            
            log.info("开始并行处理Sheet1和Sheet3 - CPU核心数: {}, 线程池最大线程数: {}", cpuCores, cpuCores * 4);
            log.info("  Sheet1: {} 条数据, 批次大小: {}, 预计批次数: {}", 
                    filteredList1.size(), batchSize1, (filteredList1.size() + batchSize1 - 1) / batchSize1);
            log.info("  Sheet3: {} 条数据, 批次大小: {}, 预计批次数: {}", 
                    filteredList2.size(), batchSize3, (filteredList2.size() + batchSize3 - 1) / batchSize3);
            
            // 获取当前日期（用于Sheet3）
            final String currentDate = LocalDate.now().toString();
            
            // 直接并行处理Sheet1（使用优化的批次大小）
            List<CompletableFuture<List<InventoryExportVO>>> sheet1Futures = new ArrayList<>();
            for (int i = 0; i < filteredList1.size(); i += batchSize1) {
                final int start = i;
                final int end = Math.min(i + batchSize1, filteredList1.size());
                final List<EmsMainEngine> batch = filteredList1.subList(start, end);
                
                CompletableFuture<List<InventoryExportVO>> future = CompletableFuture.supplyAsync(() -> {
                    List<InventoryExportVO> result = new ArrayList<>(batch.size());
                    for (EmsMainEngine item : batch) {
                        result.add(convertToInventoryExportVO(item));
                    }
                    return result;
                }, inventoryExportThreadPool);
                
                sheet1Futures.add(future);
            }
            
            // Sheet3：按料号+仓码+库位分组汇总数量
            CompletableFuture<List<InventoryStockExportVO>> sheet3Future = CompletableFuture.supplyAsync(() -> {
                // 按 料号+仓码+库位 组合分组汇总（使用 ConcurrentHashMap 保证线程安全）
                Map<String, InventoryStockExportVO> groupedMap = new java.util.concurrent.ConcurrentHashMap<>();
                
                for (EmsMainEngine item : filteredList2) {
                    String type = item.getType();
                    if (type == null || type.isEmpty()) {
                        continue;
                    }
                    
                    // 构建组合 key：料号 + 仓码 + 库位
                    String warehouse = "LP";  // 仓码固定为 LP
                    String locator = item.getLocation() != null ? item.getLocation() : "";
                    String compositeKey = type + "|" + warehouse + "|" + locator;
                    
                    // 计算当前项的数量
                    Integer totalNum = item.getTotalNum() != null ? item.getTotalNum() : 0;
                    Integer scrapNum = item.getScrapNum() != null ? item.getScrapNum() : 0;
                    Integer currentQty = totalNum - scrapNum;
                    
                    // 使用 compute 保证线程安全的累加操作
                    groupedMap.compute(compositeKey, (key, existingVO) -> {
                        if (existingVO == null) {
                            // 首次出现，创建新的 VO
                            InventoryStockExportVO vo = new InventoryStockExportVO();
                            vo.setPn(type);
                            vo.setWarehouse(warehouse);
                            vo.setLocator(locator);
                            vo.setQty(currentQty);
                            
                            // 料件类型
                            Integer canWorkOrder = item.getCanWorkOrder();
                            vo.setPntype((canWorkOrder != null && canWorkOrder == 1) ? "E" : "I");
                            
                            // 单价处理：为"-"或空时返回"0"
                            String unitPrice = item.getUnitPrice();
                            if (unitPrice == null || unitPrice.isEmpty() || "-".equals(unitPrice)) {
                                vo.setUnitprice("0");
                            } else {
                                vo.setUnitprice(unitPrice);
                            }
                            
                            vo.setInvdate(currentDate);
                            return vo;
                        } else {
                            // 已存在（料号+仓码+库位相同），累加数量（原子操作）
                            existingVO.setQty(existingVO.getQty() + currentQty);
                            return existingVO;
                        }
                    });
                }
                
                return new ArrayList<>(groupedMap.values());
            }, inventoryExportThreadPool);
            
            // ========== 并行处理Sheet2的查询和数据处理 ==========
            // 异步查询Sheet2数据，与Sheet1、Sheet3并行
            CompletableFuture<List<AverageConsumptionExportVO>> sheet2Future = CompletableFuture.supplyAsync(() -> {
                long sheet2StartTime = System.currentTimeMillis();
                List<AverageConsumptionExportVO> list = this.baseMapper.getAverageConsumptionData(startDateTime, endDateTime);
                long sheet2QueryTime = System.currentTimeMillis();
                log.debug("Sheet2 SQL查询耗时: {}ms, 数据量: {}", (sheet2QueryTime - sheet2StartTime), list.size());
                
                // 填充单耗区间
                String consumptionPeriod = startDate.toString() +
                        " - " + endDate.toString();
                
                // 批量获取Redis数据（优化：使用Pipeline批量查询）
                List<String> workOrderNums = list.stream()
                        .map(this::getWorkOrderNumFromVO)
                        .filter(StrUtil::isNotBlank)
                        .distinct()
                        .collect(Collectors.toList());
                
                // ===== 关键优化：使用Redis Pipeline批量查询，大幅减少网络往返 =====
                Map<String, String> productNameMap = new HashMap<>();
                if (!workOrderNums.isEmpty()) {
                    try {
                        long redisPipelineStart = System.currentTimeMillis();
                        
                        // 使用executePipelined批量查询
                        List<Object> results = redisTemplate.executePipelined(
                            (RedisCallback<Object>) connection -> {
                                for (String workOrderNum : workOrderNums) {
                                    String redisKey = workOrderNum + ":finished_product_name";
                                    connection.get(redisKey.getBytes());
                                }
                                return null;
                            }
                        );
                        
                        // 将结果映射回Map
                        for (int i = 0; i < workOrderNums.size(); i++) {
                            Object result = results.get(i);
                            if (result != null) {
                                productNameMap.put(workOrderNums.get(i), result.toString());
                            }
                        }
                        
                        long redisPipelineEnd = System.currentTimeMillis();
                        log.debug("Redis Pipeline批量查询耗时: {}ms, 查询数量: {}, 命中数量: {}", 
                                (redisPipelineEnd - redisPipelineStart), workOrderNums.size(), productNameMap.size());
                        
                    } catch (Exception e) {
                        log.error("Redis Pipeline批量查询失败，降级为单个查询", e);
                        // 降级方案：单个查询
                        for (String workOrderNum : workOrderNums) {
                            try {
                                String redisKey = workOrderNum + ":finished_product_name";
                                Object finishedProductName = redisTemplate.opsForValue().get(redisKey);
                                if (finishedProductName != null) {
                                    productNameMap.put(workOrderNum, finishedProductName.toString());
                                }
                            } catch (Exception ex) {
                                log.warn("从Redis获取成品名称失败，工单号: {}", workOrderNum);
                            }
                        }
                    }
                }
                
                // 填充数据
                for (AverageConsumptionExportVO vo : list) {
                    vo.setConsumptionPeriod(consumptionPeriod);
                    String workOrderNum = getWorkOrderNumFromVO(vo);
                    if (StrUtil.isNotBlank(workOrderNum) && productNameMap.containsKey(workOrderNum)) {
                        vo.setFinishedProductName(productNameMap.get(workOrderNum));
                    }
                }
                
                long sheet2EndTime = System.currentTimeMillis();
                log.debug("Sheet2 总处理耗时: {}ms", (sheet2EndTime - sheet2StartTime));
                
                return list;
            }, inventoryExportThreadPool);
            
            // ========== 等待所有异步任务完成并合并结果 ==========
            // 合并Sheet1结果
            List<InventoryExportVO> exportList = new ArrayList<>(filteredList1.size());
            for (CompletableFuture<List<InventoryExportVO>> future : sheet1Futures) {
                exportList.addAll(future.join());
            }
            
            // 获取Sheet3结果（已按料号分组汇总）
            List<InventoryStockExportVO> inventoryStockList = sheet3Future.join();
            
            // 获取Sheet2结果
            List<AverageConsumptionExportVO> averageConsumptionList = sheet2Future.join();
            
            long convertEndTime = System.currentTimeMillis();
            log.info("所有Sheet并行处理完成，总耗时: {}ms", (convertEndTime - convertStartTime));
            log.info("  - Sheet1: {} 条", exportList.size());
            log.info("  - Sheet2: {} 条", averageConsumptionList.size());
            log.info("  - Sheet3: {} 条", inventoryStockList.size());
            
            // ========== 导出多个Sheet ==========
            List<List<?>> dataLists = Arrays.asList(exportList, averageConsumptionList, inventoryStockList);
            List<Class<?>> classes = Arrays.asList(InventoryExportVO.class, AverageConsumptionExportVO.class, InventoryStockExportVO.class);
            List<String> sheetNames = Arrays.asList("料件基本资料", "平均单耗（BOM）", "盘点库存");
            
            FastExcelUtils.exportMultiTypeSheetExcel(
                dataLists,
                classes,
                "研发账册对接表",
                sheetNames,
                response
            );
            long exportEndTime = System.currentTimeMillis();
            
            log.info("==================== 导出完成统计 ====================");
            log.info("Sheet1（料件基本资料）: {} 条", exportList.size());
            log.info("Sheet2（平均单耗BOM）: {} 条", averageConsumptionList.size());
            log.info("Sheet3（盘点库存）: {} 条", inventoryStockList.size());
            log.info("性能统计：");
            log.info("  - SQL查询（Sheet1+Sheet3并行）: {}ms", (queryEndTime - queryStartTime));
            log.info("  - 数据转换（Sheet1+Sheet2+Sheet3并行）: {}ms", (convertEndTime - convertStartTime));
            log.info("  - 文件写入: {}ms", (exportEndTime - convertEndTime));
            log.info("总耗时: {}ms", (exportEndTime - startTime));
            log.info("===================================================");
            
        } catch (BusinessException e) {
            log.error("导出盘点数据业务异常: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("导出盘点数据失败", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "导出盘点数据失败: " + e.getMessage());
        }
    }
    
    @Override
    public Object getInventoryDataPage(InventoryDataPageRequest request) {
        log.info("开始分页查询盘点数据，请求参数: {}", request);
        long startTime = System.currentTimeMillis();
        
        try {
            // 参数校验
            if (StrUtil.isBlank(request.getStartDate()) || StrUtil.isBlank(request.getEndDate())) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "开始日期和结束日期不能为空");
            }
            
            // 校验 type 参数
            Integer type = request.getType();
            if (type == null || type < 1 || type > 2) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, 
                    "非法的type值，type必须为1（平均单耗BOM）或2（盘点库存）");
            }
            
            // 解析日期字符串
            LocalDate startDate;
            LocalDate endDate;
            try {
                startDate = LocalDate.parse(request.getStartDate());
                endDate = LocalDate.parse(request.getEndDate());
            } catch (Exception e) {
                log.error("日期格式解析失败: startDate={}, endDate={}", request.getStartDate(), request.getEndDate(), e);
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "日期格式错误，请使用yyyy-MM-dd格式");
            }
            
            // 校验开始日期不能晚于结束日期
            if (startDate.isAfter(endDate)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "开始日期不能晚于结束日期");
            }
            
            // 将LocalDate转换为LocalDateTime（一天的开始和结束）
            LocalDateTime startDateTime = startDate.atStartOfDay();
            LocalDateTime endDateTime = endDate.atTime(23, 59, 59);
            
            // 公共参数准备
            int pageNum = request.getPageNum();
            int pageSize = request.getPageSize();
            
            // 根据 type 参数查询并直接返回对应的 PageData
            Object result;
            if (type == 1) {
                // ========== 查询Sheet2数据：平均单耗BOM ==========
                result = querySheet2Data(startDateTime, endDateTime, startDate, endDate, pageNum, pageSize);
                
            } else {
                // ========== 查询Sheet3数据：盘点库存 ==========
                // 需要过滤的状态值列表
                List<Integer> excludeStatusList = Arrays.asList(
                    ModelStatusEnum.BEFORE_EDIT.getValue(),
                    ModelStatusEnum.BEFORE_STORE.getValue(),
                    ModelStatusEnum.CHANGE_BEFORE_EDIT.getValue(),
                    ModelStatusEnum.CHANGE_BEFORE_STORE.getValue(),
                    ModelStatusEnum.MODIFICATION_BEFORE_EDIT.getValue(),
                    ModelStatusEnum.MODIFICATION_BEFORE_STORE.getValue()
                );
                
                List<String> excludeDatas = emsWorkOrderDetailMapper.getWorkOrderDData();
                Integer scrapedStatus = ModelStatusEnum.SCRAPED.getValue();
                
                result = querySheet3Data(startDateTime, endDateTime, 
                    excludeStatusList, excludeDatas, scrapedStatus, pageNum, pageSize);
            }
            
            long endTime = System.currentTimeMillis();
            log.info("分页查询盘点数据完成，type={}, 耗时: {}ms", type, (endTime - startTime));
            
            return result;
            
        } catch (BusinessException e) {
            log.error("分页查询盘点数据业务异常: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("分页查询盘点数据失败", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "分页查询盘点数据失败: " + e.getMessage());
        }
    }
    
    @Override
    public List<InventoryExportVO> getMaterialInfo(MaterialInfoRequest request) {
        log.info("开始查询料件基本资料，请求参数: {}", request);
        long startTime = System.currentTimeMillis();
        
        try {
            // 参数校验
            if (StrUtil.isBlank(request.getPn())) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "PN料号不能为空");
            }
            
            String pn = request.getPn();
            
            // 需要过滤的状态值列表
            List<Integer> excludeStatusList = Arrays.asList(
                ModelStatusEnum.BEFORE_EDIT.getValue(),
                ModelStatusEnum.BEFORE_STORE.getValue(),
                ModelStatusEnum.CHANGE_BEFORE_EDIT.getValue(),
                ModelStatusEnum.CHANGE_BEFORE_STORE.getValue(),
                ModelStatusEnum.MODIFICATION_BEFORE_EDIT.getValue(),
                ModelStatusEnum.MODIFICATION_BEFORE_STORE.getValue()
            );
            
            List<String> excludeDatas = emsWorkOrderDetailMapper.getWorkOrderDData();
            LocalDateTime filterDate = LocalDateTime.of(2025, 10, 31, 23, 59, 59);
            Integer scrapedStatus = ModelStatusEnum.SCRAPED.getValue();
            
            // 查询条件
            LambdaQueryWrapper<EmsMainEngine> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.select(
                    EmsMainEngine::getType,
                    EmsMainEngine::getBigCategory,
                    EmsMainEngine::getCategory,
                    EmsMainEngine::getWorkOrderNumber,
                    EmsMainEngine::getCanWorkOrder,
                    EmsMainEngine::getUnitPrice,
                    EmsMainEngine::getInstoreDate,
                    EmsMainEngine::getModelStatus
                )
                .eq(EmsMainEngine::getType, pn)
                .notIn(EmsMainEngine::getModelStatus, excludeStatusList)
                .ne(EmsMainEngine::getCanWorkOrder, 2)
                .not(wrapper -> wrapper
                    .le(EmsMainEngine::getInstoreDate, filterDate)
                    .eq(EmsMainEngine::getModelStatus, scrapedStatus)
                );
            
            if (excludeDatas != null && !excludeDatas.isEmpty()) {
                queryWrapper.notIn(EmsMainEngine::getFixedNo, excludeDatas);
            }
            
            // 查询数据
            List<EmsMainEngine> rawData = this.list(queryWrapper);
            
            // 应用层按 type 去重（保留第一条）
            Map<String, EmsMainEngine> uniqueMap = new LinkedHashMap<>();
            for (EmsMainEngine item : rawData) {
                String itemType = item.getType();
                if (itemType != null && !itemType.isEmpty() && !uniqueMap.containsKey(itemType)) {
                    uniqueMap.put(itemType, item);
                }
            }
            
            // 转换数据
            List<InventoryExportVO> result = uniqueMap.values().stream()
                .map(this::convertToInventoryExportVO)
                .collect(Collectors.toList());
            
            long endTime = System.currentTimeMillis();
            log.info("查询料件基本资料完成，PN={}, 数量={}, 耗时: {}ms", pn, result.size(), (endTime - startTime));
            
            return result;
            
        } catch (BusinessException e) {
            log.error("查询料件基本资料业务异常: {}", e.getMessage());
            throw e;
        } catch (Exception e) {
            log.error("查询料件基本资料失败", e);
            throw new BusinessException(ErrorCode.SYSTEM_ERROR, "查询料件基本资料失败: " + e.getMessage());
        }
    }
    
    /**
     * 查询Sheet2数据：平均单耗BOM
     * @return 直接返回PageData对象
     */
    private InventoryDataPageVO.PageData<AverageConsumptionExportVO> querySheet2Data(
            LocalDateTime startDateTime, LocalDateTime endDateTime,
            LocalDate startDate, LocalDate endDate,
            int pageNum, int pageSize) {
        log.debug("开始查询Sheet2数据：平均单耗BOM");
        
        // 查询平均单耗数据
        List<AverageConsumptionExportVO> allData = this.baseMapper.getAverageConsumptionData(startDateTime, endDateTime);
        
        // 填充单耗区间
        String consumptionPeriod = startDate.toString() + " - " + endDate.toString();
        
        // 批量获取Redis数据
        List<String> workOrderNums = allData.stream()
                .map(this::getWorkOrderNumFromVO)
                .filter(StrUtil::isNotBlank)
                .distinct()
                .collect(Collectors.toList());
        
        Map<String, String> productNameMap = new HashMap<>();
        if (!workOrderNums.isEmpty()) {
            try {
                // 使用Redis Pipeline批量查询
                List<Object> results = redisTemplate.executePipelined(
                    (RedisCallback<Object>) connection -> {
                        for (String workOrderNum : workOrderNums) {
                            String redisKey = workOrderNum + ":finished_product_name";
                            connection.get(redisKey.getBytes());
                        }
                        return null;
                    }
                );
                
                // 将结果映射回Map
                for (int i = 0; i < workOrderNums.size(); i++) {
                    Object res = results.get(i);
                    if (res != null) {
                        productNameMap.put(workOrderNums.get(i), res.toString());
                    }
                }
            } catch (Exception e) {
                log.warn("Redis Pipeline批量查询失败，降级为单个查询", e);
                // 降级方案：单个查询
                for (String workOrderNum : workOrderNums) {
                    try {
                        String redisKey = workOrderNum + ":finished_product_name";
                        Object finishedProductName = redisTemplate.opsForValue().get(redisKey);
                        if (finishedProductName != null) {
                            productNameMap.put(workOrderNum, finishedProductName.toString());
                        }
                    } catch (Exception ex) {
                        log.warn("从Redis获取成品名称失败，工单号: {}", workOrderNum);
                    }
                }
            }
        }
        
        // 填充数据
        for (AverageConsumptionExportVO vo : allData) {
            vo.setConsumptionPeriod(consumptionPeriod);
            String workOrderNum = getWorkOrderNumFromVO(vo);
            if (StrUtil.isNotBlank(workOrderNum) && productNameMap.containsKey(workOrderNum)) {
                vo.setFinishedProductName(productNameMap.get(workOrderNum));
            }
        }
        
        // 分页处理
        long total = allData.size();
        int fromIndex = (pageNum - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, allData.size());
        
        List<AverageConsumptionExportVO> records = fromIndex < allData.size()
            ? allData.subList(fromIndex, toIndex)
            : new ArrayList<>();
        
        // 构建分页数据
        InventoryDataPageVO.PageData<AverageConsumptionExportVO> pageData = new InventoryDataPageVO.PageData<>();
        pageData.setPageNum(pageNum);
        pageData.setPageSize(pageSize);
        pageData.setTotal(total);
        pageData.setTotalPages((int) Math.ceil((double) total / pageSize));
        pageData.setRecords(records);
        pageData.setHasNext(pageNum < pageData.getTotalPages());
        pageData.setHasPrevious(pageNum > 1);
        
        log.debug("Sheet2查询完成：总数={}, 当前页={}", total, records.size());
        return pageData;
    }
    
    /**
     * 查询Sheet3数据：盘点库存
     * @return 直接返回PageData对象
     */
    private InventoryDataPageVO.PageData<InventoryStockExportVO> querySheet3Data(
            LocalDateTime startDateTime, LocalDateTime endDateTime,
            List<Integer> excludeStatusList, List<String> excludeDatas,
            Integer scrapedStatus, int pageNum, int pageSize) {
        log.debug("开始查询Sheet3数据：盘点库存");
        
        // 查询条件
        LambdaQueryWrapper<EmsMainEngine> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(
                EmsMainEngine::getType,
                EmsMainEngine::getLocation,
                EmsMainEngine::getTotalNum,
                EmsMainEngine::getScrapNum,
                EmsMainEngine::getCanWorkOrder,
                EmsMainEngine::getUnitPrice,
                EmsMainEngine::getInstoreDate,
                EmsMainEngine::getModelStatus
            )
            .le(EmsMainEngine::getInstoreDate, endDateTime)
            .ne(EmsMainEngine::getCanWorkOrder, 2)
            .notIn(EmsMainEngine::getModelStatus, excludeStatusList)
            .ne(EmsMainEngine::getModelStatus, scrapedStatus);
        
        if (excludeDatas != null && !excludeDatas.isEmpty()) {
            queryWrapper.notIn(EmsMainEngine::getFixedNo, excludeDatas);
        }
        
        // 查询全部数据
        List<EmsMainEngine> allData = this.list(queryWrapper);
        
        // 分组汇总（按料号+仓码+库位）
        String currentDate = LocalDate.now().toString();
        Map<String, InventoryStockExportVO> groupedMap = new HashMap<>();
        
        for (EmsMainEngine item : allData) {
            String itemType = item.getType();
            if (itemType == null || itemType.isEmpty()) {
                continue;
            }
            
            // 构建组合 key
            String warehouse = "LP";
            String locator = item.getLocation() != null ? item.getLocation() : "";
            String compositeKey = itemType + "|" + warehouse + "|" + locator;
            
            // 计算数量
            Integer totalNum = item.getTotalNum() != null ? item.getTotalNum() : 0;
            Integer scrapNum = item.getScrapNum() != null ? item.getScrapNum() : 0;
            Integer currentQty = totalNum - scrapNum;
            
            // 分组汇总
            if (groupedMap.containsKey(compositeKey)) {
                InventoryStockExportVO existingVO = groupedMap.get(compositeKey);
                existingVO.setQty(existingVO.getQty() + currentQty);
            } else {
                InventoryStockExportVO vo = new InventoryStockExportVO();
                vo.setPn(itemType);
                vo.setWarehouse(warehouse);
                vo.setLocator(locator);
                vo.setQty(currentQty);
                
                Integer canWorkOrder = item.getCanWorkOrder();
                vo.setPntype((canWorkOrder != null && canWorkOrder == 1) ? "E" : "I");
                
                String unitPrice = item.getUnitPrice();
                if (unitPrice == null || unitPrice.isEmpty() || "-".equals(unitPrice)) {
                    vo.setUnitprice("0");
                } else {
                    vo.setUnitprice(unitPrice);
                }
                
                vo.setInvdate(currentDate);
                groupedMap.put(compositeKey, vo);
            }
        }
        
        // 转换为List并分页
        List<InventoryStockExportVO> allRecords = new ArrayList<>(groupedMap.values());
        long total = allRecords.size();
        
        int fromIndex = (pageNum - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, allRecords.size());
        
        List<InventoryStockExportVO> records = fromIndex < allRecords.size()
            ? allRecords.subList(fromIndex, toIndex)
            : new ArrayList<>();
        
        // 构建分页数据
        InventoryDataPageVO.PageData<InventoryStockExportVO> pageData = new InventoryDataPageVO.PageData<>();
        pageData.setPageNum(pageNum);
        pageData.setPageSize(pageSize);
        pageData.setTotal(total);
        pageData.setTotalPages((int) Math.ceil((double) total / pageSize));
        pageData.setRecords(records);
        pageData.setHasNext(pageNum < pageData.getTotalPages());
        pageData.setHasPrevious(pageNum > 1);
        
        log.debug("Sheet3查询完成：总数={}, 当前页={}", total, records.size());
        return pageData;
    }
    
    /**
     * 从AverageConsumptionExportVO中获取工单号
     * @param vo 平均单耗导出VO
     * @return 工单号
     */
    private String getWorkOrderNumFromVO(AverageConsumptionExportVO vo) {
        return vo.getWorkOrderNum();
    }
    
    /**
     * 动态计算最优批次大小
     * 根据数据量和CPU核心数动态调整批次大小，以平衡并行度和线程切换开销
     * 
     * @param totalSize 总数据量
     * @param cpuCores CPU核心数
     * @return 最优批次大小
     */
    private int calculateOptimalBatchSize(int totalSize, int cpuCores) {
        if (totalSize == 0) {
            return 1000;
        }
        
        // 目标并行任务数：线程池最大线程数的1.5倍，确保充分利用线程池
        int targetParallelTasks = cpuCores * 4 * 3 / 2;
        
        // 根据数据量计算批次大小
        int calculatedBatchSize = Math.max(100, totalSize / targetParallelTasks);
        
        // 批次大小的上下限
        final int MIN_BATCH_SIZE = 100;    // 最小批次：避免过度拆分
        final int MAX_BATCH_SIZE = 2000;   // 最大批次：避免单个任务过重
        
        // 限制在合理范围内
        int optimalBatchSize = Math.max(MIN_BATCH_SIZE, Math.min(MAX_BATCH_SIZE, calculatedBatchSize));
        
        return optimalBatchSize;
    }
    
    /**
     * 转换单条数据为导出VO（线程安全，优化版）
     * @param item 原始数据
     * @return 导出VO
     */
    private InventoryExportVO convertToInventoryExportVO(EmsMainEngine item) {
        InventoryExportVO vo = new InventoryExportVO();
        
        // 直接赋值，减少方法调用开销
        vo.setPn(item.getType());
        vo.setPurchaseunit("PCS");
        vo.setUnit("PCS");
        vo.setUnitDesc("PCS");

        String bigCategory = item.getBigCategory();
        String category = item.getCategory();
        
        vo.setBigCategory(bigCategory);
        vo.setCategory(category);
        vo.setSpec(bigCategory+"-"+category);
        
        // 内联关务品名判断逻辑，避免额外方法调用
        String customsName;
        if (bigCategory == null || bigCategory.isEmpty()) {
            customsName = category;
        } else if ("Unit".equalsIgnoreCase(bigCategory) || "Part".equalsIgnoreCase(bigCategory)) {
            customsName = category;
        } else if ("Machine".equalsIgnoreCase(bigCategory)) {
            customsName = "笔记本电脑";
        } else if ("PCBA".equalsIgnoreCase(bigCategory)) {
            customsName = "主板";
        } else {
            customsName = category;
        }
        vo.setCustomsName(customsName);
        
        // 物料属性判断（使用三元运算符，更快）
        vo.setMaterialProperty((item.getCanWorkOrder() != null && item.getCanWorkOrder() == 1) ? "E" : "I");
        
        // 单价处理：为"-"或空时返回"0"
        String unitPrice = item.getUnitPrice();
        if (unitPrice == null || unitPrice.isEmpty() || "-".equals(unitPrice)) {
            vo.setUnitprice("0");
        } else {
            vo.setUnitprice(unitPrice);
        }
        
        vo.setCurrency("USD");
        
        return vo;
    }
    
    /**
     * 获取关务品名
     * @param bigCategory 大类别
     * @param category 细分类别
     * @return 关务品名
     */
    private String getCustomsName(String bigCategory, String category) {
        if (StrUtil.isBlank(bigCategory)) {
            return category;
        }
        
        // Unit或Part：取category的值
        if ("Unit".equalsIgnoreCase(bigCategory) || "Part".equalsIgnoreCase(bigCategory)) {
            return category;
        }
        // Machine：写死成"笔记本电脑"
        else if ("Machine".equalsIgnoreCase(bigCategory)) {
            return "笔记本电脑";
        }
        // PCBA：写死成"主板"
        else if ("PCBA".equalsIgnoreCase(bigCategory)) {
            return "主板";
        }
        // 其他情况返回category
        else {
            return category;
        }
    }
    
    /**
     * 转换单条数据为盘点库存导出VO（已废弃，Sheet3 现在按料号分组汇总）
     * @deprecated 已由分组汇总逻辑替代，不再使用
     */
    @Deprecated
    private InventoryStockExportVO convertToInventoryStockExportVO(EmsMainEngine item, String currentDate) {
        InventoryStockExportVO vo = new InventoryStockExportVO();
        
        // 第1列：料号
        vo.setPn(item.getType());
        
        // 第2列：仓码（写死LP）
        vo.setWarehouse("LP");
        
        // 第3列：库位
        vo.setLocator(item.getLocation());
        
        // 第4列：数量 = totalNum - scrapNum
        Integer totalNum = item.getTotalNum() != null ? item.getTotalNum() : 0;
        Integer scrapNum = item.getScrapNum() != null ? item.getScrapNum() : 0;
        vo.setQty(totalNum - scrapNum);
        
        // 第5列：料件类型（canWorkOrder == 1 为成品，否则为部品）
        Integer canWorkOrder = item.getCanWorkOrder();
        vo.setPntype((canWorkOrder != null && canWorkOrder == 1) ? "E" : "I");
        
        // 第6列：单价处理：为"-"或空时返回"0"
        String unitPrice = item.getUnitPrice();
        if (unitPrice == null || unitPrice.isEmpty() || "-".equals(unitPrice)) {
            vo.setUnitprice("0");
        } else {
            vo.setUnitprice(unitPrice);
        }
        
        // 第7列：日期（年月日）
        vo.setInvdate(currentDate);
        
        return vo;
    }

    // ==================== 缓存管理方法 ====================
    
    /**
     * 通用缓存获取方法
     */
    private <T> T getFromCache(String key, Class<T> clazz) {
        try {
            Object cached = redisTemplate.opsForValue().get(key);
            return clazz.cast(cached);
        } catch (Exception e) {
            log.warn("从缓存获取数据失败，key: {}, 错误: {}", key, e.getMessage());
            return null;
        }
    }

    /**
     * 通用缓存保存方法
     */
    private void saveToCache(String key, Object value, long expireMinutes) {
        try {
            redisTemplate.opsForValue().set(key, value, expireMinutes, TimeUnit.MINUTES);
            log.debug("缓存保存成功，key: {}, 过期时间: {}分钟", key, expireMinutes);
        } catch (Exception e) {
            log.warn("缓存数据失败，key: {}, 错误: {}", key, e.getMessage());
        }
    }
    
}

